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

408

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

42,087
11
52
90

score 43 · Answer 2 · answered Oct 10 '11 at 09:00

43

Assuming Sql server

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

answered Oct 10 '11 at 09:00

V4Vendetta

35,718
7
75
81

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 29 · Answer 3 · answered Oct 10 '11 at 08:55

29

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

408,740
77
567
659

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 20 · Answer 4 · answered Jun 22 '16 at 12:18

20

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

301
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

125,519
44
317
399

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,456
9
32
40

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

641
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 2 · Answer 9 · 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

230,875
49
477
553

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 10 · 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

230,875
49
477
553

answered May 29 '17 at 11:19

Metin Özsoy

21
1

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

2

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

edited Oct 10 '11 at 18:27

Rob

5,122
5
38
61

answered Oct 10 '11 at 09:49

sagar Shah

89
1
1
3

score 1 · Answer 12 · 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

641
6
19

answered Oct 23 '13 at 05:02

Arif Ansari

432
1
4
12

score 1 · Answer 13 · answered May 15 '15 at 09:36

1

To get the time from datetime, we can use

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

answered May 15 '15 at 09:36

thevan

9,684
52
134
197

score 1 · Answer 14 · 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 15 · 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 16 · 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,395
3
21
45

score 0 · Answer 17 · 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

559
4
9

score -2 · Answer 18 · edited Nov 08 '16 at 11:36

-2

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,259
2
21
30

answered Nov 08 '16 at 11:28

user7131338

11

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?

18 Answers18

Linked

Related