select with datetime problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select with datetime problem

Author

Topic

solent
Starting Member

33 Posts

Posted - 2006-01-03 : 12:53:31

[CODE]
ALTER PROCEDURE [dbo].[usp_Sel_ErrorRecord]
@ERR_USERNAME varchar(200) = NULL,
@ERR_NUMBER int = NULL,
@ERR_PROCEDURE varchar(126) = NULL,
@ERR_TIME datetime = NULL,
@ErrorTime datetime OUTPUT,
@UserName varchar(200) OUTPUT,
@ErrorNumber int OUTPUT,
@ErrorSeverity int OUTPUT,
@ErrorState int OUTPUT,
@ErrorProcedure varchar(126) OUTPUT,
@ErrorLine int OUTPUT,
@ErrorMessage varchar(4000) OUTPUT

AS
BEGIN
SET NOCOUNT ON;

SET @ERR_USERNAME = CONVERT(sysname, @ERR_USERNAME)

SELECT @ErrorTime = ErrorTime,
@UserName = UserName,
@ErrorNumber = ErrorNumber,
@ErrorSeverity = ErrorSeverity,
@ErrorState = ErrorState,
@ErrorProcedure = ErrorProcedure,
@ErrorLine = ErrorLine,
@ErrorMessage = ErrorMessage
FROM ErrorLog
WHERE UserName = @ERR_USERNAME AND
ErrorNumber = @ERR_NUMBER AND
ErrorProcedure = @ERR_PROCEDURE AND
ErrorTime = @ERR_TIME
END
[/CODE]

i have the above sproc and i want to retrive the following record
Username: dbo
ErrorNumber: 515
ErrorProcedure: usp_Ins_User
ErrorTime: 03/01/2006 17:48:31

but it dosent work. if i remove the ErrorTime where clause it works but if not then the output params return NULL.

apparently i need some sort of conversion on the @ERR_TIME parameters but i havent yet figured out which one to use.

any ideas?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 13:11:35

You didn't provide enough info to say for sure but it's one of these:

1. you are passing NULL as input for @err_Time

2. you are passing a datetime value for @err_time that doesn't have an exact match (to the millisecond) to a value in ErrorLog.ErrorTime

Be One with the Optimizer
TG

solent
Starting Member

33 Posts

Posted - 2006-01-03 : 13:18:18

thanks TG for the reply

but i dont pass a null value and i pass the variable to @ERR_TIME to the exact match to the last milisecond.

solent
Starting Member

33 Posts

Posted - 2006-01-03 : 13:21:07

i am sure about the above because i pass the datetime variable from a c# asp.net and i debug and watch the varible from c# and it is indeed that one i posted above.

could it be something else?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 13:31:09

the one you posted above doesn't not include milliseconds. What is the datatype of ErrorTime columns? Do a select from the table to see the value you're interested in. It might be a datetime/smalldatetime issue. check this out:

select convert(smalldatetime, getdate()), getdate()
--where convert(smalldatetime, getdate()) = getdate()

uncomment the where clause and see what happens.

Be One with the Optimizer
TG

solent
Starting Member

33 Posts

Posted - 2006-01-03 : 13:42:14

i havent yet tested what you said but i just took a closer look at my application and i belive i dont inlucde the milleseconds.

i will try what you said in a bit and see how it goes.

thanks TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 13:47:40

I bet your table has errorTime as smallDateTime. It may be as simple as defining your parameter as smallDateTime


declare @err_time datetime
set @err_time = '03/01/2006 17:48:31'

select	*
from	(
	select	convert(smallDatetime, '03/01/2006 17:48:31') sdt
		,convert(Datetime, '03/01/2006 17:48:31') dt
	) a
where	sdt = @err_time
go

declare @err_time smalldatetime
set @err_time = '03/01/2006 17:48:31'

select	*
from	(
	select	convert(smallDatetime, '03/01/2006 17:48:31') sdt
		,convert(Datetime, '03/01/2006 17:48:31') dt
	) a
where	sdt = @err_time

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources