Get time from datetime field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get time from datetime field

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-03 : 09:52:33

I have a datetime column in a table and I want to get only the time.
Any idea how to do this?
Thnx!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 09:53:08

Use CONVERT() function. look into BOL for syntax.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-03 : 10:12:28

Getting Time Only from DateTime
By convention, a time only column is stored in SQL Server as an offset from 1900-01-01 00:00:00.000. The function on this link will get the time from a datetime value.
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 12:02:57

Michael has the correct answer here .... do NOT convert a perfectly good DATETIME to a string to get "just the time" -- in that case, you are not getting a time at all, just a meaningless string of characters. A bit more on "date only" and "time only" data in SQL Server here:

http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 04:33:15

Basically I need only to get the hours.I mean that I need a query to return for example all entries that have 09 as the hour in the datetime value.
How can I do this?
Thnx a lot for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 04:41:53

In Books Online there is a perfect description for HOUR() function.

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 04:46:35

Peso I have searched for this function but I could not find any documentation.
Can u give a working example so that I can try it directly to the table?
Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 05:01:12

-- Get all records for any date that has time part greater than or equal to 09:00:00.000 and less than 10:00:00.000
SELECT *
FROM Table1
WHERE HOUR(Col1) = 9

But please notice that this will degrade performance since you cannot use index (if present) for Col1.

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 05:28:16

'HOUR' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 05:30:05

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '20080304 11:34' UNION ALL
SELECT '20080304 09:15'

SELECT *
FROM @Sample
WHERE DATEPART(HOUR, dt) = 9[/code]

E 12°55'05.25"
N 56°04'39.16"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 07:10:15

I used DATEPART.It worked just as I wanted to filter the hour.Thnx a lot Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources