Time part of a DateTime Field in SQL

Question

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

score 71 · Answer 1 · edited May 21 '15 at 07:17

71

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

edited May 21 '15 at 07:17

shA.t

16,580
5
54
111

answered Sep 10 '12 at 15:23

John Woo

258,903
69
498
492

1

[visit this](http://www.sql-server-helper.com/tips/date-formats.aspx) for more help. – Sohail Dec 27 '13 at 07:58

score 38 · Accepted Answer · edited Aug 29 '16 at 01:11

38

In SQL Server if you need only the hh:mi, you can use:

DECLARE @datetime datetime

SELECT @datetime = GETDATE()

SELECT RIGHT('0'+CAST(DATEPART(hour, @datetime) as varchar(2)),2) + ':' +
       RIGHT('0'+CAST(DATEPART(minute, @datetime)as varchar(2)),2)

edited Aug 29 '16 at 01:11

dodgy_coder

12,407
10
54
67

answered Sep 10 '12 at 15:26

András Ottó

7,605
1
28
38

@LarryBud i fixed that in an edit - added the RIGHT function calls. – dodgy_coder Aug 29 '16 at 01:11

score 31 · Answer 3 · answered Sep 10 '12 at 15:24

31

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

answered Sep 10 '12 at 15:24

Taryn

242,637
56
362
405

1

This is great, it enables easy time difference calculations like this: SELECT DATEDIFF(mi, CAST(@SomeDate AS TIME),CAST(@AnotherDate AS TIME)) – Dave Feb 09 '16 at 11:56

score 10 · Answer 4 · answered Nov 11 '15 at 09:12

10

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.

answered Nov 11 '15 at 09:12

Heinzi

167,459
57
363
519

4

Cool, but with SQL Server 2008 and later it is easier to say `SELECT CAST(mydatetime AS time)` (inferred from other answers). – Jeppe Stig Nielsen Feb 05 '16 at 15:13
@JeppeStigNielsen Unless you are using DATETIMEOFFSET, then CAST(mydatetime AS time) will not work. – JumpingJezza Feb 01 '17 at 08:16
@JumpingJezza Not sure what you mean. I have a table `mytable` with a column `mydatetime` which has the type `(datetime, not null)`, and for me this works fine: `SELECT CAST(mydatetime AS time) AS TimePart, * FROM mytable` – Jeppe Stig Nielsen Feb 01 '17 at 12:25
@JeppeStigNielsen if your table `mytable` with the column `mydatetime` instead has the type `(DATETIMEOFFSET, not null)` then it will not work. DateTimeOffset is preferred to DateTime in most cases [DateTime vs DateTimeOffset](http://stackoverflow.com/q/4331189/345659) – JumpingJezza Feb 02 '17 at 03:19
@JumpingJezza That is incorrect (at least on my version, SQL Server 2014). It works fine! Surely you lose the time zone in the cast, but that is obvious. I even tried with different "scales", such as `datetimeoffest(0)`, `datetimeoffest(3)`, `datetimeoffest(7)`, etc., and `time(0)`, `time(3)`, `time(7)`, and it works in all cases. – Jeppe Stig Nielsen Feb 02 '17 at 09:56
@JeppeStigNielsen Woops you're right! Actually the error was where I was trying to add it to a date. – JumpingJezza Feb 03 '17 at 05:59
1

@JeppeStigNielsen I found that if you want to get averages of for example a daily shift finish time, CASTing the DATETIME to TIME will not work since internally SQL Server (I'm using 2019) still seems to keep the datetime representation. You end up getting what looks like an average of the DATETIME value, and not the TIME only component. By setting all the date parts to be the same, averaging the time will work. Also note that if you look at the output of the SELECT, the "zero" date component part will display as the start of the date epoch, i.e., '1900-01-01'. – z0lo Apr 22 '23 at 02:04

Nicholas Carey · Answer 5 · 2012-09-10T18:03:38.153

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

score 6 · Answer 6 · answered Jun 18 '19 at 10:53

6

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

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

answered Jun 18 '19 at 10:53

AjV Jsy

5,799
4
34
30

... but do try it for performance, because I've just experienced FORMAT() being astonishingly slow, sadly, like 2 mins vs 5 secs with or without a date format on just 100 rows. Puzzling because I've never had that issue before. – AjV Jsy Jul 12 '19 at 08:16

score 2 · Answer 7 · edited Dec 27 '13 at 07:31

2

"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'

edited Dec 27 '13 at 07:31

Sohail

574
3
21

answered Sep 10 '12 at 16:08

Jim

3,482
22
18

score 2 · Answer 8 · answered Jul 15 '15 at 18:12

2

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.

answered Jul 15 '15 at 18:12

RYUX123

191
1
6

second expression ROUND UP date, need use `floor(convert(float))` , not `convert(int)` `convert(datetime,convert(float,ok_date_added) -floor(convert(float,ok_date_added)))` – Rijen Apr 07 '17 at 18:31

score 2 · Answer 9 · answered Apr 30 '18 at 14:41

2

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

answered Apr 30 '18 at 14:41

Reza Paidar

863
4
21
51

1

This is the correct answer. I'm surprised this wasn't given as an answer earlier since the data type has existed since SQL Server 2008, well before the question was asked. – Ryan Mar 26 '19 at 16:17

score 2 · Answer 10 · answered Oct 10 '18 at 14:21

2

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!

answered Oct 10 '18 at 14:21

Glen Richmond

61
6

SELECT CAST(GETDATE() AS TIME) without the scale would be more accurate... much better answer though :) – Adam Aug 23 '19 at 22:31

score 1 · Answer 11 · edited Feb 25 '18 at 10:25

1

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'

edited Feb 25 '18 at 10:25

ata

3,398
5
20
31

answered Feb 25 '18 at 07:33

p.ajay

21
2

While this code may answer the question, providing additional context regarding **how** and **why** it solves the problem would improve the answer's long-term value. – Alexander Feb 25 '18 at 11:52

score 1 · Answer 12 · edited Sep 22 '21 at 14:57

1

declare @datetime as datetime
set @datetime = getdate()
select cast(cast(@datetime as time) as varchar(8))

edited Sep 22 '21 at 14:57

Adrian Mole

49,934
160
51
83

answered Sep 22 '21 at 09:34

Kanchan Dey

41
1
4

2

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Sep 22 '21 at 09:56
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 22 '21 at 10:07
this answer was flagged for low quality, please better the answer with more supporting info and an explanation – Noam Yizraeli Sep 23 '21 at 09:02

score 0 · Answer 13 · answered Mar 28 '21 at 17:58

0

For year:

SELECT DATEPART(YEAR, '2021-03-21' );

For hour:

SELECT DATEPART(HOUR, '2021-03-21 08:50:30' );

answered Mar 28 '21 at 17:58

milad bahari javan

310
2
6

Time part of a DateTime Field in SQL

13 Answers13

Linked