How to get Time from DateTime format in SQL?

Question

I want to get only Time from DateTime column using SQL query using SQL Server 2005 and 2008 Default output:

AttDate                   
==
2011-02-09 13:09:00    
2011-02-09 14:10:00    

I'd like this output:

AttDate                Time 
==
2011-02-09 13:09:00    13:09
2011-02-09 14:10:00    14:10

Solution 1

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

Solution 2

Assuming Sql server

SELECT CONVERT(VARCHAR(8),GETDATE(),108)

Solution 3

SQL Server 2008+ has a "time" datatype

SELECT 
    ..., CAST(MyDateTimeCol AS time)
FROM
   ...

For older versions, without varchar conversions

SELECT 
    ..., DATEADD(dd, DATEDIFF(dd, MyDateTimeCol, 0), MyDateTimeCol)
FROM
   ...

Solution 4

The simplest way to get the time from datetime without millisecond stack is:

SELECT convert(time(0),getDate())

Solution 5

Try using this

• Date to Time

  select cast(getdate() as time(0))
  

• Time to TinyTime

  select cast(orig_time as time(0))
  

View more solutions

Comments

[Naveen Babu]

search for to_char method in sql. you can specify the format and get the desired output

[rahularyansharma]

select convert(varchar(10), getdate(), 108)

[V4Vendetta]

It could be SELECT CONVERT(VARCHAR(8),GETDATE(),108) for sql server

[rahularyansharma]

why you not use this SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond, CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly is there any performance issue ?

[ZygD]

@rahularyansharma: I don't use varchar conversion for dates if needed

[rahularyansharma]

sir i want to know is there any performance decrease if we use this instead of your solution ?

[ZygD]

@rahularyansharma: you can test yourself based on this stackoverflow.com/questions/133081/…

[xr280xr]

For anyone not following, 0 represents the min date 1900-01-01. So this gets the (negative) number of days between the column value and 0, then adds those negative days to the column value which "zeros out" the date portion to 1900-01-01 and you're left with only the time.

[captainsac]

Does not work gives error 'to_char' is not a recognized built-in function name.

[Aparna]

It works only on getdate() where as when I pass select convert(varchar(8),'2011-02-09 13:09:00',108) it is producing the first 8 characters from the date .how to fix this ?

[Sayed Muhammad Idrees]

why do i Get 9 in front of the Time.. Result : 9 11:21PM