Select the time part of a date.

Question

Post reply

Select the time part of a date.

mic.con87

SSCommitted

Points: 1947
More actions
December 6, 2011 at 6:16 am

#261069

Hi,
I'm new to SQl Server and was previously working in Oracle. I had a simple query to extract the time from a date:
select to_char(round(incident_date,'HH24'), 'HH24:MI') Time
from cla_event
I've tried
select cast(IncidentDate as time) from Staging.ClaEvents
but my output is 22:37:00.0000000, I require 23:00:00 ie time must be rounded up or down and the extra milliseconds erased.
Thanks in advance

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Find 'hh:mi:ss' in following article. 😉

CAST and CONVERT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187928.aspx

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Easier than that in SQL Server 2008 and above:

SELECT CAST(CURRENT_TIMESTAMP AS time)

John

Edit: just noticed the milliseconds requirement. You can either convert the above to varchar and chop off the final characters, or it might just be easier to use Dev's original suggestion.

mic.con87 SSCommitted Points: 1947 More actions · Answer 3

Sorry I really am a beginner:

So far I have managed to eliminate the milliseconds:

SELECT CAST(CURRENT_TIMESTAMP AS time(0))

Now I need it to round up or down to the closest hour. I noticed this query doesn't work:

SELECT ROUND(CURRENT_TIMESTAMP,0)

I receive error: Msg 257, Level 16, State 3, Line 1

Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.

I also tried these both and I also get error messages

SELECT round(CAST(CURRENT_TIMESTAMP AS time(0)),0)

SELECT CAST(Round(CURRENT_TIMESTAMP,0)AS time(0))

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Please try convert with style 108.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Lowell SSC Guru Points: 323518 More actions · Answer 5

Dev (12/6/2011)
Please try convert with style 108.
http://msdn.microsoft.com/en-us/library/ms187928.aspx

i would disagree; you want to stay in the same datatypes whenever possible.

/*--results

09:00:50.9600000

09:00:00.0000000

*/

--SQL2008 has the new Time datatype as well as the ability to assign values upon declaration

declare @TheTime time = getdate()

print @TheTime

--using dateadd and datediff, strip to the hour. (0 is acutally 0:00:00.000(midnight) when fiddling with Time datatypes

select @TheTime = DATEADD(hh, DATEDIFF(hh,0,@TheTime), 0)

print @TheTime

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Plus 30 mins to round up:

SELECT CAST(DATEADD(hour,DATEDIFF(hour,0,DATEADD(minute,30,GETDATE())),0) AS TIME(0))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mic.con87 SSCommitted Points: 1947 More actions · Answer 7

Thank-you everybody for your insight. ChrisM@Work your solution is exactly what I needed.

rahulalgude Grasshopper Points: 18 More actions · Answer 8

SELECT CONVERT(Varchar(8),CURRENT_TIMESTAMP,108 ) as Time

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Cool, thanks for the feedback. I learned the trick from an article Lowell did years ago 😀 Thanks, Lowell.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

ChrisM@Work (12/6/2011)
Cool, thanks for the feedback. I learned the trick from an article Lowell did years ago 😀 Thanks, Lowell.

Cool trick with 5 nested function call... :unsure:

SELECT CAST(DATEADD(hour,DATEDIFF(hour,0,DATEADD(minute,30,GETDATE())),0) AS TIME(0))

mic.con87 SSCommitted Points: 1947 More actions · Answer 11

Quick question: Can I mark this question as answered and add to the reputation of those that helped?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 12

mic.con87 (12/6/2011)
Quick question: Can I mark this question as answered and add to the reputation of those that helped?

Nice Thought! But it's not required... Chris is well reputed here... To be more accurate 'Hall of Fame' 😉

Lowell SSC Guru Points: 323518 More actions · Answer 13

ChrisM@Work (12/6/2011)
Cool, thanks for the feedback. I learned the trick from an article Lowell did years ago 😀 Thanks, Lowell.

Lol you used it last, and I don't really remember posting that trick, so it's your code example now!

so....

Thanks for the neat rounding trick Chris!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!