SQL – How to Get Time from DateTime Format in SQL Server

datetimesql-servert-sql

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

Best Answer

SQL Server 2008:

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

Earlier versions:

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

Related Solutions

SQL – How to Get Date from Datetime in SQL Server

The reason why your query doesn't return the row you expect, is because GETDATE() returns the date and time portion at the moment the query was executed. The value in your DateCreated column will not match the time portion, so no rows are returned.

There are various ways to construct a query so that it evaluates the date based on only the date component. Here's one example:

WHERE YEAR(datecreated) = YEAR(GETDATE())
  AND MONTH(datecreated) = MONTH(GETDATE())
  AND DAY(datecreated) = DAY(GETDATE())

The unfortunate reality is that any query using a function on the column means that if an index exists on the column, it can't be used.

Best Answer

Related Solutions

SQL – How to Get Date from Datetime in SQL Server

Related Question