Date Only Ugh. - 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

General SQL Server Forums

New to SQL Server Programming

Date Only Ugh.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-04-30 : 12:27:36

I took this code from Jeff's Blog:

dateadd(dd,0, datediff(dd,0,@DateTime))

If I adapt it as follows:
ladetail.fedatetime >= dateadd(dd,0, datediff(dd,-1,'2009-04-11 00:00:01'))

Does the minus 1 for Datediff give me the entire day of 4/11 because I am asking for midnight on from the day before?

Does this make sense?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-30 : 12:31:14

No, if you want the day before, do this:

dateadd(dd,-1, datediff(dd,0,'2009-04-11 00:00:01'))

or this:

dateadd(dd,0, datediff(dd,0,'2009-04-11 00:00:01')-1)

Can't remember where I got this as had it years, but it may help you understand it better, if someone reading this recognises their own code, thanks:


--previous Sunday
select dateadd(day, 1 - datepart(dw, getdate()), getdate())
--following Saturday
select dateadd(day, 7 - datepart(dw, getdate()), getdate())

--First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

-- This example sets Sunday as the first day of the week,
-- but by changing the DATEFIRST setting any day of the week could be the first
-- day of the week. 
set DATEFIRST 7
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

--Midnight for the Current Day
select dateadd(dd, datediff(dd,0,getdate()), 0)

--Last Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))

--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))

--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

--First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 13:54:50

It's easy to test it to see if it does what you want:

SELECT dateadd(dd,0, datediff(dd,-1,'2009-04-11 00:00:01'))

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-30 : 23:20:23

select dateadd(s,1,datediff(d,0,getdate()))

ladetail.fedatetime >= dateadd(s,1,datediff(d,0,getdate()))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources