SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert TimeStamp to time only
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jchoudja
Starting Member

USA
41 Posts

Posted - 03/14/2013 :  11:45:15  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 03/14/2013 :  12:27:35  Show Profile  Reply with Quote
SELECT CAST(t.ClockInDateTime AS TIME)

Edited by - Lamprey on 03/14/2013 12:28:00
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 03/14/2013 :  13:10:10  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 03/14/2013 :  13:19:41  Show Profile  Reply with Quote
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
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 03/15/2013 :  10:30:38  Show Profile  Reply with Quote
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
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 03/15/2013 :  10:49:49  Show Profile  Reply with Quote
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
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 03/18/2013 :  09:18:03  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000