How to get Time from DateTime format in SQL?

Question

SQL Server 2008:

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

Earlier versions:

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

Assuming Sql server

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

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

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

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

Try using this

• Date to Time

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

• Time to TinyTime

  select cast(orig_time as time(0))
  

Try this, it will work:

CONVERT(VARCHAR(8),DATETIME,114)

For your reference.

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