March 27, 2012 at 12:19 am
Do you guys know how to remove the minutes and seconds from a datetime? Instead of 2012-03-27 12:57:21.097, I should get 2012-03-27 12:00:00.000. Not sure what convert or cast expressions to use. Thanks!
March 27, 2012 at 12:24 am
Select CAST(
(
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( GETDATE() ) ) + '/' +
STR( DAY( GETDATE() ) )
)
AS DATE
)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 27, 2012 at 12:43 am
Does this work for you?
USE tempdb
GO
DECLARE @testDate DATETIME = '2012-03-27 12:57:21.097'
SELECT DATEADD(HH,DATEPART(HH,@testDate),CAST(CAST(@testDate AS DATE) AS DATETIME)) AS StrippedDate
/*------------------------
-----------------------
StrippedDate
-----------------------
2012-03-27 12:00:00.000
------------------------*/
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 27, 2012 at 2:29 am
A search of this forum should probably have yielded this solution which I've already posted:
SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-03-27 12:57:21.097'), 0)
No need to use CAST or CONVERT!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 7:32 am
convert(datetime,convert(char,getdate(),102))
The inner convert removes the time portion. The outer convert brings it back to date
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
March 28, 2012 at 7:53 am
eric.lyons (3/28/2012)
convert(datetime,convert(char,getdate(),102))The inner convert removes the time portion. The outer convert brings it back to date
OP wants to retain the hour, only wants to drop minutes and seconds.
March 28, 2012 at 8:00 am
okbangas (3/28/2012)
Interresting solution, I had not thought of that approach, and getting rid of the cast makes it slightly faster.
Just a slight change to the code shown here[/url].
To make it work with seconds, however, you need a more recent date as using the "zero" date of 1900-01-01 results in an overflow condition.
March 28, 2012 at 6:31 pm
dwain.c (3/27/2012)
A search of this forum should probably have yielded this solution which I've already posted:
SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-03-27 12:57:21.097'), 0)No need to use CAST or CONVERT!
🙂
--Jeff Moden
March 28, 2012 at 6:34 pm
Lynn:
Just a slight change to the code shown here.
To make it work with seconds, however, you need a more recent date as using the "zero" date of 1900-01-01 results in an overflow condition.
Sorry I don't understand. Are you saying this would return arithmetic overflow?
SELECT DATEADD(second, DATEDIFF(second, 0, '1900-01-01'), 0)
It did not for me when I tried it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 7:01 pm
dwain.c (3/28/2012)
Lynn:Just a slight change to the code shown here.
To make it work with seconds, however, you need a more recent date as using the "zero" date of 1900-01-01 results in an overflow condition.
Sorry I don't understand. Are you saying this would return arithmetic overflow?
SELECT DATEADD(second, DATEDIFF(second, 0, '1900-01-01'), 0)It did not for me when I tried it.
Try this:
SELECT DATEADD(second, DATEDIFF(second, 0, GETDATE()), 0)
March 28, 2012 at 9:16 pm
Interesting... Milliseconds bombs out on the same arithmetic overflow (which is useless anyway).
I guess there are too many seconds since the base date.
Fortunately minutes still works at least until here ('2099-12-31') and the others can be used for seconds:
SELECT DATEDIFF(minute, 0, '2099-12-31')
SELECT DATEADD(second, DATEDIFF(second, '2000-01-01', '2012-03-27 12:57:21.097'), '2000-01-01')
SELECT DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0), '2012-03-27 12:57:21.097'), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
Now we're getting messy!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 10:43 pm
dwain.c (3/28/2012)
Interesting... Milliseconds bombs out on the same arithmetic overflow (which is useless anyway).I guess there are too many seconds since the base date.
Fortunately minutes still works at least until here ('2099-12-31') and the others can be used for seconds:
SELECT DATEDIFF(minute, 0, '2099-12-31')SELECT DATEADD(second, DATEDIFF(second, '2000-01-01', '2012-03-27 12:57:21.097'), '2000-01-01')
SELECT DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0), '2012-03-27 12:57:21.097'), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
Now we're getting messy!
If you want to truncate to seconds or less, combine computations:
DATEADD(second, DATEDIFF(second, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0), GETDATE()), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
March 28, 2012 at 10:46 pm
Lynn - Looks like the third (hidden) line on my last post!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply