Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Date Manipulation with DATEADD/DATEDIFF Expand / Collapse
Author
Message
Posted Wednesday, April 7, 2010 10:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:24 PM
Points: 17,807, Visits: 15,728
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!


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
Post #899254
Posted Thursday, April 8, 2010 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 8, 2010 5:03 PM
Points: 2, Visits: 1
And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?
Post #899400
Posted Thursday, April 8, 2010 4:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 8, 2010 5:03 PM
Points: 2, Visits: 1
I concur... thanks Seth, great article and I have added these tips and documentation to my notes.
Post #899402
Posted Thursday, April 8, 2010 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:56 AM
Points: 20, Visits: 60
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!
Post #899421
Posted Thursday, April 8, 2010 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:56 AM
Points: 20, Visits: 60
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!
Post #899425
Posted Thursday, April 8, 2010 7:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 35,347, Visits: 31,885
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #899532
Posted Thursday, April 8, 2010 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:45 AM
Points: 215, Visits: 587
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
Post #899630
Posted Thursday, April 8, 2010 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:49 PM
Points: 1,519, Visits: 4,074
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #899667
Posted Thursday, April 8, 2010 1:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:41 PM
Points: 140, Visits: 142
Is this because SQL Server stores dates as a numeric value? Then CONVERT to VARCHAR() would cause an implicit conversion?
Post #899977
Posted Friday, April 9, 2010 7:30 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
Thank you for the article. This function has always thrown me off on SQL server.

I dreaded dates before this! Thanks again.


--
Post #900498
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse