Reputation: 3147

How to get Time from DateTime format in 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

Upvotes: 260

Answers (20)

Mizanur Rahman

Reputation: 11

FORMAT(GETDATE(),'hh:mm tt') AS Time

Output: 09:37 AM

Upvotes: 1

V4Vendetta

Reputation: 38200

Assuming Sql server

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

Upvotes: 50

user19369134

Reputation: 31

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

Output:

2:24pm

Upvotes: 3

Majid Basirati

Reputation: 2855

You can use this:

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

Output:

08:24

Upvotes: 1

jinhr

Reputation: 81

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.

Upvotes: 2

t-clausen.dk

Reputation: 44316

SQL Server 2008:

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

Earlier versions:

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

Upvotes: 447

aVB.NetCoder

Reputation: 11

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

Upvotes: 1

Code

Reputation: 739

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

Upvotes: 0

Luis Gerardo López Salazar

Reputation: 299

Get date of server

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

If it is stored in the table

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

Result:

11:41AM

Upvotes: 4

user8498521

Reputation: 61

Try this, it will work:

CONVERT(VARCHAR(8),DATETIME,114)

For your reference.

Upvotes: 6

ChinoNoypi

Reputation: 21

I often use this script to get Time from DateTime:

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

Upvotes: 2

Metin Özsoy

Reputation: 21

select cast (as time(0))

would be a good clause. For example:

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

Upvotes: 2

Balaji N

Reputation: 51

Try this:

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

Upvotes: 4

Arif Ansari

Reputation: 480

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.

Upvotes: 2

user7131338

Reputation: 21

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

Upvotes: -1

Cantarero

Reputation: 111

Try using this

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

Upvotes: 11

BigDaddy

Reputation: 351

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

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

Upvotes: 25

thevan

Reputation: 10344

To get the time from datetime, we can use

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

Upvotes: 2

sagar Shah

Reputation: 99

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

Upvotes: 3

gbn

Reputation: 432180

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

Upvotes: 39

How to get Time from DateTime format in SQL?

Answers (20)

Related Questions