How to get Time from DateTime format in SQL?
Sql ServerTsqlSql Server-2005DatetimeSql Server Problem Overview
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
Sql Server Solutions
Solution 1 - Sql Server
SQL Server 2008:
SELECT cast(AttDate as time) [time]
FROM yourtable
Earlier versions:
SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable
Solution 2 - Sql Server
Assuming Sql server
SELECT CONVERT(VARCHAR(8),GETDATE(),108)
Solution 3 - Sql Server
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
...
Solution 4 - Sql Server
The simplest way to get the time from datetime without millisecond stack is:
SELECT convert(time(0),getDate())
Solution 5 - Sql Server
Try using this
-
Date to Time
select cast(getdate() as time(0)) -
Time to TinyTime
select cast(orig_time as time(0))
Solution 6 - Sql Server
Solution 7 - Sql Server
Try this:
select convert(nvarchar,CAST(getdate()as time),100)
Solution 8 - Sql Server
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
Solution 9 - Sql Server
select AttDate,convert(char(5), AttDate, 108) [Time] from yourTableName
Solution 10 - Sql Server
I often use this script to get Time from DateTime:
SELECT CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108) FROM YOURTABLE
Solution 11 - Sql Server
select cast (as time(0))
would be a good clause. For example:
(select cast(start_date as time(0))) AS 'START TIME'
Solution 12 - Sql Server
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.
Solution 13 - Sql Server
To get the time from datetime, we can use
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
Solution 14 - Sql Server
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) )
Solution 15 - Sql Server
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.
Solution 16 - Sql Server
You can use this:
SELECT CONVERT(VARCHAR(5), GETDATE(),8)
Output:
08:24
Solution 17 - Sql Server
select convert(char(5), tbl_CustomerBooking.CheckInTime, 108) AS [time]
from tbl_CustomerBooking
Solution 18 - Sql Server
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