Date Manipulation with DATEADD/DATEDIFF

Question

Post reply

Date Manipulation with DATEADD/DATEDIFF

Garadin

One Orange Chip

Points: 29613
More actions
April 6, 2010 at 9:44 pm

#220049

Comments posted to this topic are about the item Date Manipulation with DATEADD/DATEDIFF
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]

Viewing 15 posts - 1 through 15 (of 40 total)

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

vinaypugalia Old Hand Points: 335 More actions · Answer 1

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....

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

nakache Mr or Mrs. 500 Points: 503 More actions · Answer 2

the fastest way to remove time from datetime is:

select CONVERT(datetime,floor(convert(float,getdate())))

Itzik Ben-Gan had a big post on Date Manipulation with benchmarking..(long time ago..)

sjsubscribe SSCrazy Points: 2080 More actions · Answer 3

For some common and uncommon date formats:

http://www.sql-server-helper.com/tips/date-formats.aspx

That site also has code for finding first/last day of month, week, and so on.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Nice article Seth. I will be adding this article to my arsenal.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

petrisor.dumitru Mr or Mrs. 500 Points: 501 More actions · Answer 5

Hi

I also use CAST(GETDATE() AS date) ... but it works only for sql server 2008 because in an earlier version date is not a type. Another point of view is that a smalldatetime use some bites for storing time even it is 00:00:000. The "date" type is more proper for this case.

____________________________________________________________________
Catalin Dumitru
For personal info please visit www.catalin-dumitru.ro

GlenParker Ten Centuries Points: 1051 More actions · Answer 6

Good article, well presented, will probably implement some of it.

Thanks!

Glen Parker 🙂

lhowe Ten Centuries Points: 1287 More actions · Answer 7

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))

tim.hulse SSC Veteran Points: 258 More actions · Answer 8

tim.hulse

SSC Veteran

Points: 258

April 7, 2010 at 6:23 am

#1147239

Neat! Will definitely use it.

mbarkell SSC Enthusiast Points: 132 More actions · Answer 9

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.

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

query performed on 2010-04-07 08:42

query results in 2010-04-05 instead of expected 2010-04-04.

My locale should be en_US.

rcorrigan SSC Rookie Points: 39 More actions · Answer 10

This is a great article with very good followup comments. I have been working with SSRS for almost three years now, and every time i need some weird or exotic formula for the Sales Reports (Date requirements include today and the last 2 Months as well as same time last year!) I have to look up different formulas. I will definitely use this article in my SQL activities. Thank you for the time and effort as well as the great contribution!:-)

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

nakache (4/6/2010)
the fastest way to remove time from datetime is:
select CONVERT(datetime,floor(convert(float,getdate())))
Itzik Ben-Gan had a big post on Date Manipulation with benchmarking..(long time ago..)

I believe this is the article you were referring to? In there, he actually mentions that his favorite technique is the DADD method, although his tests show casting as an int to be very slightly faster.

Here's a performance evaluation[/url] by Gail Shaw on her blog of the different methods.

I won't make any claims as to which of these is faster, I'm sure they each win on occasion, but for the versatility, I'll stick with Dateadd/Datediff.

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]

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

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

mbarkell SSC Enthusiast Points: 132 More actions · Answer 13

Thank you, I thought it might be a sortof locale setting. @@DateFirst is a handy thing to keep in mind.

grc-80104 Old Hand Points: 339 More actions · Answer 14

Very handy and nicely done article. I've come across situtations where I've needed to manipulate dates and will definitely keep this article handy. 🙂