selecting only records since midnight

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)

selecting only records since midnight

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2001-12-03 : 20:44:21

How do I select all records (that contain a datetime field) that are only since midnight the same day? If it's 1am, then only an hour of records are selected, if it's 10am, 10 hours, etc.

This will be run in production so it has to be able to look at getdate() and figure out when midnight was.

Is there an easy way to do this? Somehow my brain wants to go back and do some sort of LEFT and convert functions.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-03 : 20:52:57

Use DateDiff if DateDiff(d, GetDate(), YourDate) = 0 then it is the same day. Anything after midnight is today.

Damian

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2001-12-03 : 20:54:04

Thanks - I knew that this couldn't be too complicated.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 04:42:34

Do be aware, however, that DateDiff isn't (yet) sargable in SQL Server.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-04 : 04:57:44

Do you know a better, sargable way to solve this problem ?

Damian

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-12-04 : 05:19:29

why not pre-calculate the start and end values for TODAY...

ie....SELECT @TODAYSTART = GETDATE + '00:00:01', @TODAYEND = GETDATE + '23:59:59'....I'm rushing here....so the syntax isn't great (what an understatement)....

and then compare for all records between @TODAYSTART + @TODAYEND

??

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 05:56:56

(edit: this was a bit rushed before)

I don't there's much option but calculate the range ends


DECLARE @searchDay datetime
SET @searchDay = CONVERT(datetime, CONVERT(varchar, @searchTime, 112))

SELECT * FROM TableName
WHERE dateField >= @searchDay AND dateField < DATEADD(d,1,@searchDay)

Edited by - Arnold Fribble on 12/04/2001 08:08:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources