Date Manipulation with DATEADD/DATEDIFF

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Seth Phelabaum » Date Manipulation with DATEADD/DATEDIFF

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Date Manipulation with DATEADD/DATEDIFF

Rate Topic

Display Mode

Topic Options

Author

Message

Garadin

Posted Tuesday, April 06, 2010 9:44 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

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

Post #898102

vinaypugalia

Posted Tuesday, April 06, 2010 10:23 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 15, Visits: 188

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

nakache

Posted Tuesday, April 06, 2010 11:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:25 AM
Points: 3, Visits: 123

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

sjsubscribe

Posted Wednesday, April 07, 2010 12:30 AM

SSC 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

SQLRNNR

Posted Wednesday, April 07, 2010 12:45 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

petrisor.dumitru

Posted Wednesday, April 07, 2010 1:43 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 344, Visits: 87

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

GlenParker

Posted Wednesday, April 07, 2010 1:55 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:56 AM
Points: 751, Visits: 129

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

Thanks!

Glen Parker Smile

Post #898251

lhowe

Posted Wednesday, April 07, 2010 6:17 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:14 PM
Points: 739, Visits: 203

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

tim.hulse

Posted Wednesday, April 07, 2010 6:23 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 01, 2013 4:57 PM
Points: 13, Visits: 178

Neat! Will definitely use it.

Post #898410

mbarkell

Posted Wednesday, April 07, 2010 6:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 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 »

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions