Date Manipulation with DATEADD/DATEDIFF

Question

Date Manipulation with DATEADD/DATEDIFF

Viewing 11 posts - 31 through 40 (of 40 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Garadin (4/7/2010)
Thanks.
Nothing like actually having an article published that lets you see all the things that you forgot to add to it! :hehe:

I thought of the same thing with my first article publication. It is a nice learning tool.

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

SQLJeff SSC Rookie Points: 26 More actions · Answer 2

And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?

SQLJeff SSC Rookie Points: 26 More actions · Answer 3

I concur... thanks Seth, great article and I have added these tips and documentation to my notes.

Hanri Naude SSC Rookie Points: 38 More actions · Answer 4

Hi, Great to see all the ways to manipulate dates in SQL.

Here is another way of doing it rather quickly.

SELECT CAST(CAST(GETDATE() AS CHAR(11)) AS DATETIME)

Cool stuff,

Hanri

Whenever I get sad, I stop being sad and be awesome instead… true story!

Hanri Naude SSC Rookie Points: 38 More actions · Answer 5

hi,

You can change the first day of the week by using the @@DATEFIRST keyword.

http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

Regards,

Hanri

Whenever I get sad, I stop being sad and be awesome instead… true story!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

SQLJeff (4/8/2010)
And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?

Most likely... but the problem with that is as I previously stated... it uses twice as much CPU time and takes twice as long duration wise. If you're only working with a handful of rows, you certainly won't notice the difference. BUT, if you're working with many millions of rows like I usually have to, combined with other CPU saving methods, it makes all the difference in the world. Every microsecond counts for the stuff I usually have to 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)

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 7

Great article, Seth! Seeing as how I've tried method after method for accomplishing this and I've never been pleased with what I've done, I think this is clever, elegant, and darned handy. Thanks for sharing!

Regards,

Mike M

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

Hanri Naude (4/8/2010)
hi,
You can change the first day of the week by using the @@DATEFIRST keyword.
http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx
Regards,
Hanri

You can change the day of the week, but as I mentioned in an earlier comment, DATEDIFF is not affected by DATEFIRST settings. I'll add a section about that into the article when I get a moment and can submit that and a few other changes.

Also, as Jeff mentioned, converting to a char/varchar and then back to datetime is considerably slower... but we're talking about fractions of a second per row, so if you're only doing a couple it's not a big deal. For instance, doing anything to GETDATE() to store in a variable... not gonna make much of a difference. But If you need to do it to the column of a table with a million rows, you'll definitely see the difference.

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]

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

Is this because SQL Server stores dates as a numeric value? Then CONVERT to VARCHAR() would cause an implicit conversion?

Slick84 SSCertifiable Points: 5602 More actions · Answer 10

Thank you for the article. This function has always thrown me off on SQL server.

I dreaded dates before this! Thanks again.

--
:hehe:

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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

This is probably the best generic method for rounding datetime - by using varchar(N) for vaious values of N and with different type arguments instead of 101 it can round down to second, minute, hour, month, or yearas well as to day. Of course it uses more CPU than the method in the article, and that will be important sometimes.

Tom