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

Transact-SQL (2005)

dateformat question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-02 : 17:13:36

I am trying to extract data for the orders created the previous day from the table:

select * from orders t
where t.orderdate >= Prevday 12:00AM and
t.orderdate <= Prevday 11:59:59PM

orderdate is datetime datatype,
Please advice, how can I format the date to show as:Prevday 12:00AM and Prevday 11:59:59PM

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-02 : 17:16:38

select * from orders t
where t.orderdate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()-1), 0) and
t.orderdate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-02 : 18:17:02

Thanks for the quick response.
Select DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) is returning 2009-03-02 00:00:00.000, since I am looking for
2009-03-01 11:59:59PM(prev day), so are both the same? Is it okay if I use 2009-03-02 00:00:00.000.
Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-02 : 18:51:50

You'll notice that I am using "< 2009-03-02 00:00:00.000" and not "<= 2009-03-02 00:00:00.000". There is a key difference.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 23:24:45

try like this
where dateadd(dd,datediff(d,0,date),0) = dateadd(dd,datediff(dd,0,getdate()-1),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:51:00