Date Manipulation with DATEADD/DATEDIFF

Question

Date Manipulation with DATEADD/DATEDIFF

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

vinaypugalia (4/6/2010)
Great handy article !!
However, we can also achieve the same through -
SELECT CONVERT(DATETIME,DATEDIFF(dd,0,GETDATE()))
This way we can save the time taken to perform DATEADD operation though it would be quite marginal....

An interesting idea. It does seem to perform slightly better. Here's a proof using my 200K row Tally Table.

SELECT DATEADD(d, DATEDIFF(d,0,GETDATE()), 0) A

INTO #2

FROM Util..Tally

SELECT CONVERT(DATETIME,DATEDIFF(dd,0,GETDATE())) A

INTO #1

FROM Util..Tally

DROP TABLE #1

DROP TABLE #2

(200000 row(s) affected)

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 95 ms.

(200000 row(s) affected)

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 93 ms.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jedak SSCertifiable Points: 5578 More actions · Answer 2

If you are loading the date value into a datetime variable or a datetime column you only need to do the DATEDIFF.

DECLARE @dtDate datetime

SET @dtDate = datediff(d,0,getdate())

PRINT @dtDate

If you want to display the date value directly in a select then you'll need to convert it back to a datetime in some manner like the ones show in the article or in this thread. Note this method of stripping the time only works because SQL Server internally stores dates as numbers. If SQL Server were to change how it internally stores dates as numbers well then....who knows if these methods would work.

ArkWare Mr or Mrs. 500 Points: 525 More actions · Answer 3

Seth,

very nice. good explaination.

I have one question...

in the following code, isn't it returning the first of the week +/- 2 days?

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2)

--: 2010-03-01 00:00:00.000 Start of the day 2 days from now

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)

--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

Thanks,

Arkware

Garadin One Orange Chip Points: 29613 More actions · Answer 4

ArkWare (4/7/2010)
mbarkell (4/7/2010)
On my system using the query for the first day of the week gives Monday instead of Sunday which is, of course, incorrect. Is this based on the locale of the system, or is that consistent behavior all together. After all, the first day of the week is always Sunday not Monday.
mbarkell,
check @@DATEFIRST (Transact-SQL) in BOL...
Arkware

Great Question.

This is a tricky one. DATEDIFF doesn't actually honor datefirst or locale settings. Here's an article (also by Itzik Ben-Gan) that talks about it.

The reason you're getting monday is because 1/1/1900 was a monday. To adjust that particular query to get a sunday instead, you can use this:

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)

I should probably add that into the article.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 5

ArkWare (4/7/2010)
Seth,
very nice. good explaination.
I have one question...
in the following code, isn't it returning the first of the week +/- 2 days?
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2)
--: 2010-03-01 00:00:00.000 Start of the day 2 days from now
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)
--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.
Thanks,
Arkware

It is. That was a copy/paste error. Sorry about that. Those wk's should be d's.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

bcool Grasshopper Points: 10 More actions · Answer 6

Sorry - duplicate post (someone just beat me to it!) 🙂

Good article!

I think there might be some typos in the examples for finding dates in the past & future....this is what was shown:

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2)

--: 2010-03-01 00:00:00.000 Start of the day 2 days from now

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)

--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

I believe it should be:

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

--: 2010-03-01 00:00:00.000 Start of the day 2 days from now

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

--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

Larry Watson Grasshopper Points: 21 More actions · Answer 7

Thanks for taking the time. I've stored it in a handy place.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

Date manipulation is always something I have to lookup. Hopefully this can help me remember it so I can just write it.

Thanks,

Old_D SSC Veteran Points: 253 More actions · Answer 9

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) will produce today's date not Start of the day 2 days from now as you stated.

Good idea to have scripts for dates

Old_D SSC Veteran Points: 253 More actions · Answer 10

using dd with

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 2) will give you two days from now

Elaine Shafer-401422 Old Hand Points: 309 More actions · Answer 11

I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME)

Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':

DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

Not sure if this timestamp would actually happen...

Garadin One Orange Chip Points: 29613 More actions · Answer 12

Elaine Shafer-401422 (4/7/2010)
I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME)
Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':
DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'
SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)
Not sure if this timestamp would actually happen...

As mentioned in the article, Datetime is only accurate to 3ms. 999 and 998 both round up to 000. It's not your float conversion doing the rounding, it's the limitations of the datetime data type. Run this:

DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

SELECT @date

SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 13

Thanks for a good article. I've been using the CONVERT(CONVERT) technique mentioned by lhowe.

lhowe (4/7/2010)
Another method to strip the time from a date/time value and keep it as a datetime type would be: CONVERT(datetime,CONVERT(varchar,GetDate(),101))

The value of the DADD technique is the ability to apply it to weeks, months, years.

Thanks again!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Nice done, Seth. Good clear article. Heh... it's funny how these things happen... I just got done demonstrating to the folks at work how VARCHAR conversions use twice the CPU that DADD conversions do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 15

Thanks.

Nothing like actually having an article published that lets you see all the things that you forgot to add to it! :hehe:

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]