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 Tuesday, April 6, 2010 9:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #898102
Posted Tuesday, April 6, 2010 10:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 2, 2014 8:54 AM
Points: 17, Visits: 238
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
Post #898135
Posted Tuesday, April 6, 2010 11:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 7, Visits: 209
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..)
Post #898155
Posted Wednesday, April 7, 2010 12:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
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.
Post #898196
Posted Wednesday, April 7, 2010 12:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 21,617, Visits: 15,271
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #898205
Posted Wednesday, April 7, 2010 1:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 9, 2014 7:12 AM
Points: 362, Visits: 97
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
Post #898247
Posted Wednesday, April 7, 2010 1:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, March 29, 2014 11:41 AM
Points: 753, Visits: 133
Good article, well presented, will probably implement some of it.

Thanks!


Glen Parker
Post #898251
Posted Wednesday, April 7, 2010 6:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:38 PM
Points: 894, Visits: 237
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))
Post #898398
Posted Wednesday, April 7, 2010 6:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:11 AM
Points: 13, Visits: 181
Neat! Will definitely use it.
Post #898410
Posted Wednesday, April 7, 2010 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 6:46 AM
Points: 4, Visits: 25
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.
Post #898434
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse