datediff, dateadd 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

General SQL Server Forums

New to SQL Server Programming

datediff, dateadd question

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-06 : 10:24:09

I'm still in confuse about use of functions like dateadd, dateadd, datepart

I just learned we use datediff to find difference between two dates as in the following code. And learned we use dateadd to add some dates/months to the provided dates.


select datediff(d,'20120903','20120905')
select dateadd(d,5,'20120905')

But if I replace any of the above dates with '0' it gives some strange results like '4155' or something like that.

So I wonder then how come the following code from a source gives a proper date even though it is '0' in place of dates.

 select
dateadd(dd, 0, datediff(dd, 0, getdate()))

In other words, I just would like to know how this function - dateadd(dd,0,datediff(dd,0,getdaet())) works?

This may help me to use them in my database in different situations. Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 10:29:16

SQL Server is using '1900-01-01 00:00:00.000' when giving 0 as a date.

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-08 : 17:26:02

quote:
Originally posted by learning_grsql

I'm still in confuse about use of functions like dateadd, dateadd, datepart

I just learned we use datediff to find difference between two dates as in the following code. And learned we use dateadd to add some dates/months to the provided dates.

select datediff(d,'20120903','20120905')
select dateadd(d,5,'20120905')

But if I replace any of the above dates with '0' it gives some strange results like '4155' or something like that.

So I wonder then how come the following code from a source gives a proper date even though it is '0' in place of dates.
select
dateadd(dd, 0, datediff(dd, 0, getdate()))

In other words, I just would like to know how this function - dateadd(dd,0,datediff(dd,0,getdaet())) works?

This may help me to use them in my database in different situations. Thank you.

see

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-09 : 16:55:59

Thanks Visakh16 and Webfred
@visakh16,
Your blog explained to me exactly what I was looking for. Again thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 22:46:12

welcome

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-10 : 10:46:08

@visakh16
I'm just reading through more of your examples in the page and the following is your code


declare @year int, @week int
set @year = 2012
set @week = 7
;With WeekDates (Startdate, Enddate)
As
(
select dateadd (wk, @week - 1, dateadd(yy,@year - 1900,0)), Dateadd(wk, @week, dateadd(yy,@year - 1900,0)) - 1
union all
select dateadd (wk,1,startdate), Dateadd(wk,1,enddate)
from weekdates
where dateadd (wk,1,enddate) < dateadd (yy,@year - 1989,0)
)
select * from weekdates

It just gives the output below :


startdate | enddate
2012-02-12 00:00:00.000	| 2012-02-18 00:00:00.000

But for me this simple line also gives the same output :


select dateadd (wk, @week - 1, dateadd(yy,@year - 1900,0)), Dateadd(wk, @week, dateadd(yy,@year - 1900,0)) - 1

So I'm confused about the whole purpose of adding union all and CTE in your example.
Sorry if I'm being too silly here. I'm trying to make clear these functions.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 12:24:19

quote:
Originally posted by learning_grsql

@visakh16
I'm just reading through more of your examples in the page and the following is your code

declare @year int, @week int
set @year = 2012
set @week = 7
;With WeekDates (Startdate, Enddate)
As
(
select dateadd (wk, @week - 1, dateadd(yy,@year - 1900,0)), Dateadd(wk, @week, dateadd(yy,@year - 1900,0)) - 1
union all
select dateadd (wk,1,startdate), Dateadd(wk,1,enddate)
from weekdates
where dateadd (wk,1,enddate) < dateadd (yy,@year - 1989,0)
)
select * from weekdates

It just gives the output below :

startdate | enddate
2012-02-12 00:00:00.000 | 2012-02-18 00:00:00.000

But for me this simple line also gives the same output :

select dateadd (wk, @week - 1, dateadd(yy,@year - 1900,0)), Dateadd(wk, @week, dateadd(yy,@year - 1900,0)) - 1

So I'm confused about the whole purpose of adding union all and CTE in your example.
Sorry if I'm being too silly here. I'm trying to make clear these functions.

Thank you.

there was a small typo in your code

it should have been


declare @year int, @week int
set @year = 2012
set @week = 7
;With WeekDates (Startdate, Enddate)
As
(
select dateadd (wk, @week - 1, dateadd(yy,@year - 1900,0)), Dateadd(wk, @week, dateadd(yy,@year - 1900,0)) - 1
union all
select dateadd (wk,1,startdate), Dateadd(wk,1,enddate)
from weekdates
where dateadd (wk,1,enddate) < dateadd (yy,@year - 1899,0)
)
select * from weekdates

the code was to generate start and end dates for all weeks in the year
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources