InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:20:42 GMT20http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]lhowe (4/7/2010)[/b][hr]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))[/quote]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.Fri, 09 Apr 2010 22:05:48 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThank you for the article. This function has always thrown me off on SQL server. I dreaded dates before this! Thanks again.Fri, 09 Apr 2010 07:30:45 GMTSlick84http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxIs this because SQL Server stores dates as a numeric value? Then CONVERT to VARCHAR() would cause an implicit conversion?Thu, 08 Apr 2010 13:58:16 GMTElaine Shafer-401422http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]Hanri Naude (4/8/2010)[/b][hr]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).aspxRegards,Hanri[/quote]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.Thu, 08 Apr 2010 09:03:16 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxGreat 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 MThu, 08 Apr 2010 08:37:30 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]SQLJeff (4/8/2010)[/b][hr]And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?[/quote]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.Thu, 08 Apr 2010 07:11:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxhi,You can change the first day of the week by using the @@DATEFIRST keyword.http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspxRegards,HanriThu, 08 Apr 2010 05:27:30 GMTHanri Naudehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxHi, 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,HanriThu, 08 Apr 2010 05:23:06 GMTHanri Naudehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxI concur... thanks Seth, great article and I have added these tips and documentation to my notes.Thu, 08 Apr 2010 04:37:47 GMTSQLJeffhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxAnd what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?Thu, 08 Apr 2010 04:35:27 GMTSQLJeffhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]Garadin (4/7/2010)[/b][hr]Thanks.Nothing like actually having an article published that lets you see all the things that you forgot to add to it! :hehe:[/quote]I thought of the same thing with my first article publication. It is a nice learning tool.Wed, 07 Apr 2010 22:16:01 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThanks.Nothing like actually having an article published that lets you see all the things that you forgot to add to it! :hehe:Wed, 07 Apr 2010 19:25:03 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxNice done, Seth. Good clear article. Heh... it's funny how these things happen... I just got done demonstrating to the folks at work how VARCHAR conversions use twice the CPU that DADD conversions do.Wed, 07 Apr 2010 18:48:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThanks for a good article. I've been using the CONVERT(CONVERT) technique mentioned by lhowe.[quote][b]lhowe (4/7/2010)[/b][hr]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))[/quote]The value of the DADD technique is the ability to apply it to weeks, months, years.Thanks again!Wed, 07 Apr 2010 12:12:50 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]Elaine Shafer-401422 (4/7/2010)[/b][hr]I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME) Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) Not sure if this timestamp would actually happen...[/quote]As mentioned in the article, Datetime is only accurate to 3ms. 999 and 998 both round up to 000. It's not your float conversion doing the rounding, it's the limitations of the datetime data type. Run this:[code="sql"]DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'SELECT @dateSELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) [/code]Wed, 07 Apr 2010 11:26:28 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxI frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME) Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) Not sure if this timestamp would actually happen...Wed, 07 Apr 2010 10:33:05 GMTElaine Shafer-401422http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxusing dd withSELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 2) will give you two days from nowWed, 07 Apr 2010 09:37:36 GMTOld_Dhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxSELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) will produce today's date not [u]Start of the day 2 days from now[/u] as you stated.Good idea to have scripts for datesWed, 07 Apr 2010 09:34:17 GMTOld_Dhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxDate manipulation is always something I have to lookup. Hopefully this can help me remember it so I can just write it.Thanks,Wed, 07 Apr 2010 09:24:21 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThanks for taking the time. I've stored it in a handy place.Wed, 07 Apr 2010 08:19:37 GMTLarry Watsonhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxSorry - duplicate post (someone just beat me to it!) :-)Good article!I think there might be some typos in the examples for finding dates in the past & future....this is what was shown:SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) --: 2010-03-01 00:00:00.000 Start of the day 2 days from nowSELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.I believe it should be:SELECT DATEADD([b]dd[/b], DATEDIFF([b]dd[/b],0,GETDATE()), 2) --: 2010-03-01 00:00:00.000 Start of the day 2 days from nowSELECT DATEADD([b]dd[/b], DATEDIFF([b]dd[/b],0,GETDATE()), -2)--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.Wed, 07 Apr 2010 07:59:07 GMTbcoolhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]ArkWare (4/7/2010)[/b][hr]Seth,very nice. good explaination.I have one question...in the following code, isn't it returning the first of the week +/- 2 days?[code="sql"]SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) --: 2010-03-01 00:00:00.000 Start of the day 2 days from nowSELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.[/code]Thanks,Arkware[/quote]It is. That was a copy/paste error. Sorry about that. Those wk's should be d's.Wed, 07 Apr 2010 07:56:05 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]ArkWare (4/7/2010)[/b][hr][quote][b]mbarkell (4/7/2010)[/b][hr]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.[/quote]mbarkell,check @@DATEFIRST (Transact-SQL) in BOL...Arkware[/quote]Great Question.This is a tricky one. DATEDIFF doesn't actually honor datefirst or locale settings. Here's an [url=http://www.windowsitpro.com/article/sql-server/datediff-with-the-week-part.aspx]article [/url] (also by Itzik Ben-Gan) that talks about it. The reason you're getting monday is because 1/1/1900 was a monday. To adjust that particular query to get a sunday instead, you can use this:[code="sql"]SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)[/code]I should probably add that into the article.Wed, 07 Apr 2010 07:51:53 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxSeth,very nice. good explaination.I have one question...in the following code, isn't it returning the first of the week +/- 2 days?[code="sql"]SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) --: 2010-03-01 00:00:00.000 Start of the day 2 days from nowSELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.[/code]Thanks,ArkwareWed, 07 Apr 2010 07:45:14 GMTArkWarehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxIf you are loading the date value into a datetime variable or a datetime column you only need to do the DATEDIFF.[code="sql"]DECLARE @dtDate datetimeSET @dtDate = datediff(d,0,getdate())PRINT @dtDate[/code]If you want to display the date value directly in a select then you'll need to convert it back to a datetime in some manner like the ones show in the article or in this thread. Note this method of stripping the time only works because SQL Server internally stores dates as numbers. If SQL Server were to change how it internally stores dates as numbers well then....who knows if these methods would work.Wed, 07 Apr 2010 07:40:51 GMTJedakhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]vinaypugalia (4/6/2010)[/b][hr]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....[/quote]An interesting idea. It does seem to perform slightly better. Here's a proof using my 200K row Tally Table.[code="sql"]SELECT DATEADD(d, DATEDIFF(d,0,GETDATE()), 0) AINTO #2FROM Util..TallySELECT CONVERT(DATETIME,DATEDIFF(dd,0,GETDATE())) AINTO #1FROM Util..TallyDROP TABLE #1DROP TABLE #2[/code][code="plain"](200000 row(s) affected) SQL Server Execution Times: CPU time = 78 ms, elapsed time = 95 ms.(200000 row(s) affected) SQL Server Execution Times: CPU time = 62 ms, elapsed time = 93 ms.[/code]Wed, 07 Apr 2010 07:37:26 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxVery handy and nicely done article. I've come across situtations where I've needed to manipulate dates and will definitely keep this article handy. :-)Wed, 07 Apr 2010 07:33:00 GMTgrc-80104http://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThank you, I thought it might be a sortof locale setting. @@DateFirst is a handy thing to keep in mind.Wed, 07 Apr 2010 07:32:48 GMTmbarkellhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]mbarkell (4/7/2010)[/b][hr]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.[/quote]mbarkell,check @@DATEFIRST (Transact-SQL) in BOL...ArkwareWed, 07 Apr 2010 07:29:03 GMTArkWarehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspx[quote][b]nakache (4/6/2010)[/b][hr]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..)[/quote]I believe [url=http://www.sqlmag.com/article/tsql3/datetime-calculations-part-1.aspx]this is the articl[/url]e 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 [url=http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/]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.Wed, 07 Apr 2010 07:21:48 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxThis 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!:-)Wed, 07 Apr 2010 07:10:02 GMTrcorriganhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxOn 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:42query results in 2010-04-05 instead of expected 2010-04-04.My locale should be en_US.Wed, 07 Apr 2010 06:43:45 GMTmbarkellhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxNeat! Will definitely use it.Wed, 07 Apr 2010 06:23:56 GMTtim.hulsehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxAnother 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))Wed, 07 Apr 2010 06:17:16 GMTlhowehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxGood article, well presented, will probably implement some of it.Thanks!Wed, 07 Apr 2010 01:55:00 GMTGlenParkerhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxHiI 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.Wed, 07 Apr 2010 01:43:04 GMTpetrisor.dumitruhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxNice article Seth. I will be adding this article to my arsenal.Wed, 07 Apr 2010 00:45:37 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxFor some common and uncommon date formats:http://www.sql-server-helper.com/tips/date-formats.aspxThat site also has code for finding first/last day of month, week, and so on.Wed, 07 Apr 2010 00:30:05 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxthe 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..)Tue, 06 Apr 2010 23:07:49 GMTnakachehttp://www.sqlservercentral.com/Forums/Topic898102-2670-1.aspxGreat 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....Tue, 06 Apr 2010 22:23:56 GMTvinaypugalia