[sql] Time part of a DateTime Field in SQL

How would I be able to extract the time part of a DateTime field in SQL? For my project I have to return data that has a timestamp of 5pm of a DateTime field no matter what the date is

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

returns for example :- 15:19:43

replace getdate() with the date time you want to extract just time from!

This will return the time-Only

For SQL Server:

SELECT convert(varchar(8), getdate(), 108)

Explanation:

getDate() is giving current date and time.
108 is formatting/giving us the required portion i.e time in this case.
varchar(8) gives us the number of characters from that portion.
Like:
If you wrote varchar(7) there, it will give you 00:00:0
If you wrote varchar(6) there, it will give you 00:00:
If you wrote varchar(15) there, it will still give you 00:00:00 because it is giving output of just time portion. SQLFiddle Demo

For MySQL:

SELECT DATE_FORMAT(NOW(), '%H:%i:%s')

SQLFiddle Demo

SELECT DISTINCT   
                 CONVERT(VARCHAR(17), A.SOURCE_DEPARTURE_TIME, 108)  
FROM  
      CONSOLIDATED_LIST AS A  
WHERE   
      CONVERT(VARCHAR(17), A.SOURCE_DEPARTURE_TIME, 108) BETWEEN '15:00:00' AND '15:45:00'

I know this is an old question, but since the other answers all

• return strings (rather than datetimes),
• rely on the internal representation of dates (conversion to float, int, and back) or
• require SQL Server 2008 or beyond,

I thought I'd add a "pure" option which only requires datetime operations and works with SQL Server 2005+:

SELECT DATEADD(dd, -DATEDIFF(dd, 0, mydatetime), mydatetime)

This calculates the difference (in whole days) between date zero (1900-01-01) and the given date and then subtracts that number of days from the given date, thereby setting its date component to zero.

Note that from MS SQL 2012 onwards you can use FORMAT(value,'format')

e.g. WHERE FORMAT(YourDatetime,'HH:mm') = '17:00'

"For my project, I have to return data that has a timestamp of 5pm of a DateTime field, No matter what the date is."

So I think what you meant was that you needed the date, not the time. You can do something like this to get a date with 5:00 as the time:

SELECT CONVERT(VARCHAR(10), GetDate(), 110) + ' 05:00:00'

you can use CONVERT(TIME,GETDATE()) in this case:

INSERT INTO infoTbl
(itDate, itTime)
VALUES (GETDATE(),CONVERT(TIME,GETDATE()))

or if you want print it or return that time use like this:

DECLARE @dt TIME
SET @dt = CONVERT(TIME,GETDATE())
PRINT @dt

This should strip away the date part:

select convert(datetime,convert(float, getdate()) - convert(int,getdate())), getdate()

and return a datetime with a default date of 1900-01-01.

If you want only the hour of your datetime, then you can use DATEPART() - SQL Server:

declare @dt datetime
set @dt = '2012-09-10 08:25:53'

select datepart(hour, @dt) -- returns 8

In SQL Server 2008+ you can CAST() as time:

declare @dt datetime
set @dt = '2012-09-10 08:25:53'

select CAST(@dt as time) -- returns 08:25:53

Try this in SQL Server 2008:

select *
from some_table t
where convert(time,t.some_datetime_column) = '5pm'

If you want take a random datetime value and adjust it so the time component is 5pm, then in SQL Server 2008 there are a number of ways. First you need start-of-day (e.g., 2011-09-30 00:00:00.000).

• One technique that works for all versions of Microsoft SQL Server as well as all versions of Sybase is to use convert/3 to convert the datetime value to a varchar that lacks a time component and then back into a datetime value:

  select convert(datetime,convert(varchar,current_timestamp,112),112)
  

The above gives you start-of-day for the current day.

• In SQL Server 2008, though, you can say something like this:

  select start_of_day =               t.some_datetime_column
                      - convert(time, t.some_datetime_column ) ,
  from some_table t
  

  which is likely faster.

Once you have start-of-day, getting to 5pm is easy. Just add 17 hours to your start-of-day value:

select five_pm = dateadd(hour,17, t.some_datetime_column
                   - convert(time,t.some_datetime_column)
                   )
from some_table t