Convert TimeStamp to time only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert TimeStamp to time only

Author

Topic

jchoudja
Starting Member

41 Posts

Posted - 2013-03-14 : 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

41 Posts

Posted - 2013-03-14 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-14 : 12:27:35

SELECT CAST(t.ClockInDateTime AS TIME)

jchoudja
Starting Member

41 Posts

Posted - 2013-03-14 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-14 : 13:19:41

How is it not working? Error? Not formatted the wya you want it? other?

Here are some links on how to ask a database question (sample data expected output, etc..). If you follow the pattens in these links it'll be much easier for us to help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jchoudja
Starting Member

41 Posts

Posted - 2013-03-15 : 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

41 Posts

Posted - 2013-03-15 : 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

41 Posts

Posted - 2013-03-18 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources