DATEDIFF and DATEADD - 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

DATEDIFF and DATEADD

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2008-04-22 : 03:23:14

Hi
I'm trying to break down some code to work out how it's working. I've encountered DateDiff and DateAdd and I think this adds a new date of midnight today after reading up on the syntax (still dont fully understand how it works to be honest)

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

However, this is slightly different because where there is a 0 above there is a 13 in this one so I'm wondering what the 13 is actually doing.

SELECT DATEADD(dd, DATEDIFF(dd, 13, GETDATE()), 0)

In this one there's a 21

SELECT DATEADD(dd, DATEDIFF(dd, 21, GETDATE()), 0)

Can someone guide me please?

Thanks
Daniel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 03:50:13

13 and 21 is the number of days substracted from the difference of days between '19000101' and GETDATE().

I can't detrermine what your requirement are.
Do you want to add 13 and 21 hours since last midnight?

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

chipembele
Posting Yak Master

106 Posts

Posted - 2008-04-22 : 04:13:14

quote:
Originally posted by Peso

13 and 21 is the number of days substracted from the difference of days between '19000101' and GETDATE().

I can't detrermine what your requirement are.
Do you want to add 13 and 21 hours since last midnight?

Hi, thanks for your reply
i'm not trying to write something new, i'm trying to work out what a piece of code is doing.

This is the full text. I think what it's supposed doing is when the Date Now is > 13 days since the PrintedOn Date and Date Now is < 21 days since the Printed On Date then a second letter is required. Basically, If the printed on date is between 13 and 21 days since the offer letter was printed then a second letter is required.

I'm just trying to work out how it's doing it but I am struggling.

SELECT
DATEADD(dd, DATEDIFF(dd, 13, GETDATE()), 0)) > 
DATEADD(dd, DATEDIFF(dd, 0, DDOfferLetter.[PrintedOn]), 0) 
AND DATEADD(dd,DATEDIFF(dd, 21, GETDATE()), 0) 
< DATEADD(dd, DATEDIFF(dd, 0, DDOfferLetter.[PrintedOn]), 0) 
THEN 1 ELSE 0 END AS BIT) AS RequireASecondOfferLetter

i'm just interested in how this bit actually works so I can use it in future knowing I fully understand it.

DATEADD(dd, DATEDIFF(dd, 13, GETDATE()), 0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 04:33:44

I think whats its doing is checking whether DDOfferLetter value falls in range of 13-21 days before current date (i.e happens third week before current) then its setting bit as 1 otherwise 0

chipembele
Posting Yak Master

106 Posts

Posted - 2008-04-22 : 04:48:28

quote:
Originally posted by visakh16

I think whats its doing is checking whether DDOfferLetter value falls in range of 13-21 days before current date (i.e happens third week before current) then its setting bit as 1 otherwise 0

Yes, that's my thinking too. I guess I dont fully understand how the 13 and the 21 cause that function. That is the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 06:22:18

Have a look at explanation for DATEDIFF and DATEADD functions in BOL. that should give you more idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources