| Author |
 Topic  |
|
|
jchoudja
Starting Member
USA
41 Posts |
 Posted - 03/14/2013 : 11:45:15
|
Hi I need to comvert a Timstamp colom to time only.
I am already using
CONVERT(varchar, t.ClockInDateTime, 101) AS DateIn
To convert to simple date. is there a simple way like this one to convert to time only?
Thank you
jc |
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 03/14/2013 : 12:14:17
|
exemple ClockIntime = 2012-07-18 18:35:48.637
I have 1 column set like CONVERT(varchar, t.ClockInDateTime, 101) AS DateIn and DateIn is 01/15/2013
Now I want a second column to show time only as 18:35
I also notice that time is in 24h Clock I want to convert it in the mean time to 12h clock time.
Thank you.
jc |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 03/14/2013 : 12:27:35
|
| SELECT CAST(t.ClockInDateTime AS TIME) |
Edited by - Lamprey on 03/14/2013 12:28:00 |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 03/14/2013 : 13:10:10
|
Thank you but it is not working. I added it to my existing column, didn't work tryed it as single column didn't work either.
jc |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 03/15/2013 : 10:30:38
|
Here is the error.
Executed SQL statement: SELECT CAST(t.ClockInDateTime AS TIME).ToString() As Expr1 FROM dbo.Timestamp As t Error Source .Net SqlClient Data Provider Error Message Cannot call methods on time.
Table name is:
Timestamp with
User_name_|_ClockInDateTime
-------------------------------
Bill______|_2011-10-11 15:49:58.457
Bill______|_2011-10-17 11:34:19.590
Joe_______|_2011-10-18 15:57:41.237
Joe_______|_2011-10-19 14:44:04.663
Here is the output I want
Name_|_Date_______|_Time
-------------------------------------
Bill_|_10/11/2011_|_15:49 or 3:49 PM
Bill_|_17/10/2011_|_11:34 or 11:34 AM
Joe__|_18/10/2011_|_15:57 or 3:57 PM
Joe__|_19/10/2011_|_14:44 or 2:44 PM
Date and Time should be extracted from ClockInDateTime
Thank you for you great help in advance.
jc |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 03/15/2013 : 10:49:49
|
Now I am able to get Date and Time (24h) with
CONVERT (varchar, t.ClockInDateTime, 101)
CONVERT (varchar(5), t.ClockInDateTime, 8)
Name_|_Date_______|__Time
-------------------------------------
Bill_|_10/11/2011_|_15:49
Bill_|_17/10/2011_|_11:34
Joe__|_18/10/2011_|_15:57
Joe__|_19/10/2011_|_14:44
How can I get time to show in AM and PM?
jc |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 03/18/2013 : 09:18:03
|
I was finaly able to convert time in 12h using this:
RIGHT(CONVERT(varchar, t.ClockInDateTime, 100), 7) AS TimeIn
This will output time as:
3:49 PM
11:34 AM
3:57 PM
2:44 PM
Hope this will help someone else
jc |
|
|
| |
Topic |
|
Convert TimeStamp to time only
