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
This question is related to
sql-server
tsql
sql-server-2005
datetime
select cast (as time(0))
would be a good clause. For example:
(select cast(start_date as time(0))) AS 'START TIME'
Assuming Sql server
SELECT CONVERT(VARCHAR(8),GETDATE(),108)
You can use this:
SELECT CONVERT(VARCHAR(5), GETDATE(),8)
Output:
08:24
The simplest way to get the time from datetime without millisecond stack is:
SELECT convert(time(0),getDate())
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
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) )
Try this:
select convert(nvarchar,CAST(getdate()as time),100)
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
...
Try using this
Date to Time
select cast(getdate() as time(0))
Time to TinyTime
select cast(orig_time as time(0))
select convert(char(5), tbl_CustomerBooking.CheckInTime, 108) AS [time]
from tbl_CustomerBooking
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
To get the time from datetime, we can use
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
I often use this script to get Time from DateTime:
SELECT CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108) FROM YOURTABLE
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.
select AttDate,convert(char(5), AttDate, 108) [Time] from yourTableName
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
Source: Stackoverflow.com