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

3

18 Answers

SQL Server 2008:

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

Earlier versions:

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

0

Assuming Sql server

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

1

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

5

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.

Try this:

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

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

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

select cast (as time(0))

would be a good clause. For example:

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

I often use this script to get Time from DateTime:

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

1

To get the time from datetime, we can use

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

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

1. datatype
2. Column/Value
3. Style: Available styles are from 100 to 114. You can choose within range from. Choose one by one to change the date format.

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

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.

You can use this:

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

Output:

08:24

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

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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy