selecting only hour and minute from datetime.

Question

selecting only hour and minute from datetime.

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
December 9, 2011 at 7:44 am

#391088

How can I select only Hour and Minute from datetime without any seconds part.
I need only 09:43 from ' 2011-12-09 09:43:36.743 '

Viewing 9 posts - 1 through 8 (of 8 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

WangcChiKaBastar (12/9/2011)
How can I select only Hour and Minute from datetime without any seconds part.
I need only 09:43 from ' 2011-12-09 09:43:36.743 '

SELECT CONVERT(TIME,'2011-12-09 09:43:36.743')

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

Thanks, I think I need the date as well.

so I will need '2011-12-09 09:50' from 2011-12-09 09:50:34.600

in SQL Server 2005.

prashant-507002 SSC Eights! Points: 922 More actions · Answer 3

select {fn extract(hour from datetimeColumnName)},datetimeColumnNamefrom [TableName]

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

WangcChiKaBastar (12/9/2011)
Thanks, I think I need the date as well.
so I will need '2011-12-09 09:50' from 2011-12-09 09:50:34.600
in SQL Server 2005.

You posted in the SQL Server 2008 forum.

DECLARE @DATE AS DATETIME

SET @DATE = '2011-12-09 09:50:34.600'

SELECT CONVERT(VARCHAR(11),DATEADD(dd, DATEDIFF(dd, 0, @DATE), 0), 20) + CONVERT(VARCHAR(5),DATEADD(dd, -DATEDIFF(dd, 0, @DATE), @DATE),14)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 5

this trick of adding the # minutes to the minimum sql date will trucate to the last minute that occurred:

select DATEADD(minute, DATEDIFF(minute,0,getdate()), 0)

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!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes, 5 Minutes, x number of Minutes, Minute, or Second.

Start of Time Period Functions:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Start of Week Function:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

select

a.DT,

DT__To_Minute = dateadd(ms,-(datepart(ss,a.DT)*1000)-datepart(ms,a.DT),a.DT)

from

( -- Test Data

select DT = convert(datetime,'2011-12-09 09:43:36.743') union all

select DT = getdate()

) a

order by

a.DT

Results:

DT DT__To_Minute

----------------------- -----------------------

2009-11-12 09:43:36.743 2009-11-12 09:43:00.000

2011-12-09 10:06:35.547 2011-12-09 10:06:00.000

(2 row(s) affected)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Lowell (12/9/2011)
this trick of adding the # minutes to the minimum sql date will trucate to the last minute that occurred:
select DATEADD(minute, DATEDIFF(minute,0,getdate()), 0)

Your code works, provided the date is within a limit that should be OK for most applications. The code I posted works with all datetime values.

-- Works OK

select DT = DATEADD(minute, DATEDIFF(minute,0,'5983-01-01 00:00:36.743'), 0)

-- Causes Overflow

select DT = DATEADD(minute, DATEDIFF(minute,0,'5984-01-01 00:00:36.743'), 0)

prashant-507002 SSC Eights! Points: 922 More actions · Answer 8

select {fn extract(hour from datetimeColumnName)} +':'+{fn extract(minute from datetimeColumnName)} ,datetimeColumnNamefrom [TableName]