Help with comparing dates in sp

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

Transact-SQL (2000)

Help with comparing dates in sp

Author

Topic

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-12 : 05:28:44

Hello All,
I have a stored procedure as follows:


ALTER PROCEDURE dbo.spGetUpdatedChats
	
	@lastUpdate DateTime OUTPUT
	
AS
Declare @XMLTime as int

	Select UserName,ChatText,(Convert(varchar(20),ChatDate,101)+' '
	+(SELECT dbo.FixHour(Convert(varchar(20),ChatDate,108)))) as ChDate from Chats  
where Hidden='N' and ChatDate>@lastUpdate order by ChatDate DESC FOR XML AUTO ;
	Select @lastUpdate=Max(ChatDate) from Chats
RETURN

The problem with the code is that the stored procedure always seems to be doing a ChatDate>=@lastUpdate instead of a > than comparison. For example, if I execute the sp with a parameter of '5/12/2006 2:35:37 PM', the resultset will contain all rows >= a date of 5/12/2006 2:35:37 PM.
Sample resultset:
<Chats UserName="TEST" ChatText="Test2" ChDate="05/12/2006 2:37:00 PM"/>
<Chats UserName="TEST" ChatText="test" ChDate="05/12/2006 2:35:37 PM"/>

Can someone tell me whats wrong?

Oh and just in case, here is the code for my UDF (sucks that there isn't a CONVERT parameter that outputs in 'mm/dd/yyyy hh:mm:ss AM/PM')


ALTER FUNCTION dbo.FixHour(@Time varchar(20))
RETURNS varchar(20)
AS
BEGIN
Declare @hour int
Declare @minsec varchar(10)
Declare @AMPM varchar(2)
Declare @cHour int
set @minsec=Substring(@Time,3,len(@Time))
set @hour=Cast(Substring(@Time,1,2) as int)

	if @hour=12 
		set @cHour=0
	else if @hour>12
		set @cHour=@hour-12
	else
		set @cHour=@hour
		
if @hour<12
	set @AMPM='AM'
else
	set @AMPM='PM'
	
return cast(@cHour as varchar(2))+@minsec+' '+@AMPM
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-12 : 08:19:02

You mean like this?
---------------------------------------------
CREATE FUNCTION dbo.fnMyOwnDateFormat
(
@WhatDate DATETIME
)
RETURNS VARCHAR(22)
AS

BEGIN
RETURN REPLACE(CONVERT(VARCHAR, @WhatDate, 101) + LEFT(RIGHT(CONVERT(VARCHAR, @WhatDate, 109), 14), 8) + ' ' + RIGHT(CONVERT(VARCHAR, @WhatDate, 109), 2), ' ', ' ')
END
---------------------------------------------

and then

---------------------------------------------
ALTER PROCEDURE dbo.spGetUpdatedChats
(
@LastUpdate DateTime OUTPUT
)
AS

SET NOCOUNT ON

SELECT UserName,
ChatText,
dbo.fnMyOwnDateFormat(ChatDate) ChDate
FROM Chats
WHERE Hidden = 'N'
AND ChatDate > @LastUpdate
ORDER BY ChatDate DESC
FOR XML AUTO

SELECT @LastUpdate = MAX(ChatDate)
FROM Chats
---------------------------------------------

Or... Directly

---------------------------------------------
ALTER PROCEDURE dbo.spGetUpdatedChats
(
@LastUpdate DateTime OUTPUT
)
AS

SET NOCOUNT ON

SELECT UserName,
ChatText,
REPLACE(CONVERT(VARCHAR, ChatDate, 101) + LEFT(RIGHT(CONVERT(VARCHAR, ChatDate, 109), 14), 8) + ' ' + RIGHT(CONVERT(VARCHAR, ChatDate, 109), 2), ' ', ' ') ChDate
FROM Chats
WHERE Hidden = 'N'
AND ChatDate > @LastUpdate
ORDER BY ChatDate DESC
FOR XML AUTO

SELECT @LastUpdate = MAX(ChatDate)
FROM Chats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-12 : 08:21:54

Also, you could try to rewrite

AND ChatDate > @LastUpdate

to

AND CONVERT(DATETIME, ChatDate) > @LastUpdate

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-12 : 09:56:37

Thanks for the nifty little script to convert the date in the right format...

ChatDate is already a DateTime type so converting it does no good, this is really puzzling me, I've been pulling my hair out trying to figure out whats wrong... as a temporary fix, I just did this:

... and Cast(ChatDate as varchar(20))>Cast(@lastUpdate as varchar(20))

and it seems to be working, I'd still like to know why comparing the dates in the datetime format doesn't seem to work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-12 : 10:00:39

can you post some sample data ?

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-12 : 10:05:49

Sure...

1 User1 Hello N 5/7/2006 3:42:00 PM
2 User2 Hello N 5/7/2006 3:43:00 PM
3 User1 TEST N 5/7/2006 3:45:00 PM
4 User2 TEST N 5/7/2006 3:48:00 PM
5 User1 TEST N 5/7/2006 4:17:40 PM
6 User2 TEST N 5/7/2006 4:19:10 PM
7 User1 TEST N 5/8/2006 10:25:28 AM
8 User2 TEST N 5/8/2006 4:38:55 PM
9 User1 TEST N 5/8/2006 4:41:30 PM
10 User2 TEST N 5/10/2006 3:52:58 PM
11 User1 TEST N 5/10/2006 3:53:20 PM
12 User2 TEST N 5/10/2006 3:53:32 PM
13 User1 TEST N 5/10/2006 3:53:45 PM
14 User3 TEST N 5/11/2006 11:32:00 AM
15 User1 TEST N 5/11/2006 11:35:00 AM
16 User2 TEST N 5/11/2006 11:40:00 AM
17 User5 TEST N 5/11/2006 3:41:17 PM
29 TEST TEST N 5/12/2006 1:36:04 PM
30 TEST TEST N 5/12/2006 2:32:18 PM
31 User5 Yep! N 5/12/2006 2:34:40 PM
32 User6 Nope N 5/12/2006 2:35:04 PM
33 TEST Nope N 5/12/2006 2:35:37 PM

BTW, I should mention that this is SQL Server 2005 Express

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-12 : 10:18:06

Can you try this code ? Did it gives you the result you want ?

declare	@lastUpdate	datetime
select	@lastUpdate	= '5/12/2006 2:35:37 PM'
select	*
from	yourtable
where	ChatDate	> @lastUpdate

And what does FixHour do ? Just strip off the minutes ?

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-12 : 10:26:15

Still no good khtan. Yeah, fixhour just converts the date into this format: "5/7/2006 3:42:00 PM". If you use XML auto with the query and just select the date, it converts it into a weird format so I had to reformat it but Peso's REPLACE(CONVERT(VARCHAR, ChatDate, 101) + LEFT(RIGHT(CONVERT(VARCHAR, ChatDate, 109), 14), 8) + ' ' + RIGHT(CONVERT(VARCHAR, ChatDate, 109), 2), ' ', ' ') ChDate does the same trick.

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-13 : 10:40:02

I haven't figured out the problem yet and casting the comparison to varchar didn't seem to work to well either so I tried splitting up the date using datepart but that didn't work so this I just added one second to the ChatDate using dateAdd in the conditional clause and thats the best I could do until I figure out whats going on:
DateAdd(s,1,ChatDate)>@lastUpdate

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-13 : 13:00:39

If you column is datetime, the values will be more precise than your sample output. ie:
"2006-05-13 12:56:52.250" rather than 5/13/2006 12:56 PM

I suspect you have dates that appear like 5/13/2006 12:56 PM when formatted
but are really greater than that: 2006-05-13 12:56:52.250

Be One with the Optimizer
TG

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2006-05-13 : 13:13:11

Ahh yes, that seems like the only logical explanation TG. When I do a plain query, it just shows up to the seconds not milliseconds (I'm using Visual Studio 2005's IDE for querying and SQL Server 2005 Express). However, when I do a DatePart(ms,ChatDate) it does indeed give me milliseconds. So I'll just cull the milliseconds from my app's insert logic and I should be all clear...
Thanks a billion TG!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources