Get start and end of given day.

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 2005 Forums

Transact-SQL (2005)

Get start and end of given day.

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-04-09 : 05:09:49

Hi,

Getdate() will get me the date, but how I can calculate two other datetime values corresponding to the very first second and the very last second of the current day?

Eg.

Today is 2008-04-09 <whatever time>
Startdate should be 2008-04-09 00:00:01
Enddate should be 2008-04-09 23:59:59

Cheers,
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:16:18

Better to use

Startdate: DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
EndDate: DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')

and write query like this

select * from table
where date >= @startdate
and date < @enddate

E 12°55'05.25"
N 56°04'39.16"

mattt
Posting Yak Master

194 Posts

Posted - 2008-04-09 : 05:19:39

Lovely, thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-09 : 05:19:47

[code]select
dateadd(second, 1, dateadd(day, datediff(day, 0, getdate()), 0)) as StartDate,
dateadd(second, -1, dateadd(day, datediff(day, 0, getdate())+1, 0)) as EndDate[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 05:19:54

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '23:59:59')[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 05:29:33

The best approach is applying this in where clause


where datecol>=dateadd(day, datediff(day, 0, getdate()), 0)) and
      datecol<dateadd(day, datediff(day, 0, getdate())+1, 0))

for better accuracy

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources