DateDiff error -simple - 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)

DateDiff error -simple

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-02-08 : 10:30:38

SELECT LastUpdate
FROM dbo.StatSysTrend
WHERE (DATEDIFF(day, LastUpdate, '8/27/05') <= 1)

While I think I am getting any thing within a day of
'8/27/05' I actually get a whole lot of dates,
Same for when I choose hour and <= 23... The value in my LastUpdate carries full hours and minutes.... but trying this did not help
WHERE (DATEDIFF(day, LastUpdate, '8/27/05 11:00:00') <= 1)

andrewcw

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 11:30:32

I'm not really sure which date you are after.

Try it this way to get the day before 2005/8/27


SELECT
	LastUpdate
FROM
	dbo.StatSysTrend
WHERE 
	LastUpdate >= dateadd(dd,-1,'20050827') and
	LastUpdate  < '20050827'

Or this for the day of 2005/8/27:

SELECT
	LastUpdate
FROM
	dbo.StatSysTrend
WHERE 
	LastUpdate >= '20050827' and
	LastUpdate  < dateadd(dd,1,'20050827')

CODO ERGO SUM

andrewcw
Posting Yak Master

133 Posts

Posted - 2006-02-08 : 11:42:30

Yes for the day of 2005/8/27 - that's exactly what I need. Thanks ! ( I noticed the datediff function seemed to work with getdate, but not the date I put it. I think the 2nd date must be represented differently ..? )

andrewcw

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 11:56:17

quote:
Originally posted by andrewcw

Yes for the day of 2005/8/27 - that's exactly what I need. Thanks ! ( I noticed the datediff function seemed to work with getdate, but not the date I put it. I think the 2nd date must be represented differently ..? )

andrewcw

The date format YYYYMMDD is SQL Server's date format that is independant of any local/regional server settings, so it is the best way to represent a date in a string format.

Doing the query as a range of
DateColumn >= StartDate and
DateColumn < EndDate
is best, because it allows SQL Server to use any indexes that exist on the date column, and you don't have to execute a function against each row in the table.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources