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

search for to_char method in sql. you can specify the format and get the desired output — Naveen Babu, Oct 10 '11 at 08:55
It could be `SELECT CONVERT(VARCHAR(8),GETDATE(),108)` for sql server — V4Vendetta, Oct 10 '11 at 09:00

t-clausen.dk · Accepted Answer · 2020-02-04T08:18:11.887

433

SQL Server 2008:

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

Earlier versions:

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

edited Feb 04 '20 at 08:18

answered Oct 10 '11 at 08:57

t-clausen.dk

43,517
12
59
92

score 49 · Answer 2 · edited Aug 17 '22 at 16:38

49

Assuming Sql server

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

edited Aug 17 '22 at 16:38

scohe001

15,110
2
31
51

answered Oct 10 '11 at 09:00

V4Vendetta

37,194
9
78
82

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 ? – Aparna Aug 04 '17 at 06:31

score 35 · Answer 3 · answered Oct 10 '11 at 08:55

35

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
   ...

answered Oct 10 '11 at 08:55

gbn

422,506
82
585
676

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 ? – rahularyansharma Oct 10 '11 at 09:02
2

@rahularyansharma: I don't use varchar conversion for dates if needed – gbn Oct 10 '11 at 09:03
sir i want to know is there any performance decrease if we use this instead of your solution ? – rahularyansharma Oct 10 '11 at 09:04
1

@rahularyansharma: you can test yourself based on this http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime – gbn Oct 10 '11 at 09:05
2

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. – xr280xr Oct 13 '15 at 20:55

score 22 · Answer 4 · answered Jun 22 '16 at 12:18

22

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

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

answered Jun 22 '16 at 12:18

BigDaddy

321
2
2

score 11 · Answer 5 · edited Sep 24 '16 at 18:24

11

Try using this

Date to Time
```
select cast(getdate() as time(0))
```
Time to TinyTime
```
select cast(orig_time as time(0))
```

edited Sep 24 '16 at 18:24

Tunaki

132,869
46
340
423

answered Sep 24 '16 at 17:56

Cantarero

111
1
2

score 6 · Answer 6 · edited Aug 22 '17 at 06:58

6

Try this, it will work:

CONVERT(VARCHAR(8),DATETIME,114)

For your reference.

edited Aug 22 '17 at 06:58

Bugs

4,491
9
32
41

answered Aug 22 '17 at 06:12

user8498521

61
1
1

score 4 · Answer 7 · edited Apr 06 '17 at 20:36

4

Try this:

select  convert(nvarchar,CAST(getdate()as time),100)

edited Apr 06 '17 at 20:36

Raj Baral

661
6
19

answered Feb 13 '14 at 12:30

Balaji N

51
1
1

score 4 · Answer 8 · answered Sep 06 '18 at 16:45

4

Get date of server

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) FROM TABLENAME WHERE ...

or

If it is stored in the table

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), datename, 100), 7)) FROM TABLENAME WHERE ...

Result:

11:41AM

answered Sep 06 '18 at 16:45

Luis Gerardo López Salazar

289
3
4

score 3 · Answer 9 · edited Oct 10 '11 at 18:27

3

select AttDate,convert(char(5), AttDate, 108) [Time] from yourTableName

edited Oct 10 '11 at 18:27

Rob

5,223
5
41
62

answered Oct 10 '11 at 09:49

sagar Shah

99
1
1
3

score 2 · Answer 10 · edited Aug 04 '17 at 07:53

2

I often use this script to get Time from DateTime:

SELECT CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108) FROM YOURTABLE

edited Aug 04 '17 at 07:53

Cody Gray - on strike

239,200
50
490
574

answered Oct 23 '14 at 03:50

ChinoNoypi

21
1

why do i Get 9 in front of the Time.. Result : 9 11:21PM – Sayed Muhammad Idrees Jun 23 '19 at 18:38

score 2 · Answer 11 · answered May 15 '15 at 09:36

2

To get the time from datetime, we can use

SELECT CONVERT(VARCHAR(20), GETDATE(), 114)

answered May 15 '15 at 09:36

thevan

10,052
53
137
202

score 2 · Answer 12 · edited Aug 04 '17 at 07:53

2

select cast (as time(0))

would be a good clause. For example:

(select cast(start_date as time(0))) AS 'START TIME'

edited Aug 04 '17 at 07:53

Cody Gray - on strike

239,200
50
490
574

answered May 29 '17 at 11:19

Metin Özsoy

21
1

score 2 · Answer 13 · edited Jun 23 '22 at 18:32

2

Declare @date Datetime = '06/18/2021 14:24:31';
Select FORMAT(@date, 'h\:m tt', 'en-US') As Timey

Output:

2:24pm

edited Jun 23 '22 at 18:32

Jeremy Caney

7,102
69
48
77

answered Jun 19 '22 at 12:29

user19369134

21
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 19 '22 at 19:39

score 1 · Answer 14 · edited Apr 06 '17 at 19:06

1

If you want date something in this style: Oct 23 2013 10:30AM

Use this

SELECT CONVERT(NVARCHAR(30),getdate(), 100)

convert() method takes 3 parameters

datatype
Column/Value
Style: Available styles are from 100 to 114. You can choose within range from. Choose one by one to change the date format.

edited Apr 06 '17 at 19:06

Raj Baral

661
6
19

answered Oct 23 '13 at 05:02

Arif Ansari

472
1
4
12

score 1 · Answer 15 · edited Jan 28 '20 at 21:35

1

on MSSQL2012 or above

cast(dateadd(ms,datediff(ms, [StartDateTime], [StopDateTime]),0) as Time(0))

...or...

convert(time(0),dateadd(ms,datediff(ms, [StartDateTime], [StopDateTime]),0) )

edited Jan 28 '20 at 21:35

answered Jan 28 '20 at 21:17

aVB.NetCoder

11
1

score 1 · Answer 16 · answered May 04 '20 at 19:09

1

SQL Server 2012:

Select TRY_CONVERT(TIME, myDateTimeColumn) from myTable;

Personally, I prefer TRY_CONVERT() to CONVERT(). The main difference: If cast fails, TRY_CONVERT() returns NULL while CONVERT() raises an error.

answered May 04 '20 at 19:09

jinhr

71
6

score 1 · Answer 17 · answered Jan 03 '21 at 04:59

1

You can use this:

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

Output:

08:24

answered Jan 03 '21 at 04:59

Majid Basirati

2,665
3
24
46

score 0 · Answer 18 · answered Sep 19 '19 at 10:59

0

select convert(char(5), tbl_CustomerBooking.CheckInTime, 108) AS [time]
from tbl_CustomerBooking

answered Sep 19 '19 at 10:59

Code

679
5
9

score -1 · Answer 19 · edited Nov 08 '16 at 11:36

-1

select substr(to_char(colUmn_name, 'DD/MM/RRRR HH:MM:SS'),11,19) from table_name;

Output: from

05:11:26
05:11:24
05:11:24

edited Nov 08 '16 at 11:36

Alex Szabo

3,274
2
18
30

answered Nov 08 '16 at 11:28

user7131338

21

Does not work gives error `'to_char' is not a recognized built-in function name.` – captainsac Nov 08 '16 at 11:39

How to get Time from DateTime format in SQL?

19 Answers19

Linked

Related