DATEADD and DATEDIFF in same statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

DATEADD and DATEDIFF in same statement?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-23 : 16:43:57

DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME))) AS TRDATE,

I am looking that statement as add, delete, add?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-23 : 17:03:37

is there a question

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-23 : 18:01:25

First you subtract L_LAST_TIME seconds from TR_DATETIME column value.
Then you calculate the number of days passed since 1900-01-01.
Finally you add the number of days passed since 1900-01-01 to 1900-01-01 to get a datetime value without time part.

Essentially you withdraw L_LAST_TIME seconds from TR_DATETIME and then truncate the time part and keep only the date part.

N 56°04'39.26"
E 12°55'05.63"

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2012-01-23 : 19:00:39

When troubleshooting nested date functions, I find it easier to break it down:

@smallest = DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME)
@smaller = DATEDIFF(dd,0, @smallest)
@TRDATE = DATEADD(dd,0,@smaller)

Then you can copy/paste thm into eachother:

@smaller = DATEDIFF(dd,0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME))

Then

@TRDATE = DATEADD(dd,0,DATEDIFF(dd,0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME)))

finally put in your select as

DATEADD(dd,0,DATEDIFF(dd,0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME))) As TRDATE

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-03-09 : 11:02:20

datediff ss

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:05:05

quote:
Originally posted by AdamWest

DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME))) AS TRDATE,

I am looking that statement as add, delete, add?

whats your doubt?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources