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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change data type? or strings to timestamp column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 03/15/2013 :  11:19:35  Show Profile  Reply with Quote
Hi,

I have a table hat has a column named 'Timestamp' that is of data type 'char'. Unfortunately it needs to be ofthis data type, even though it makes the 'timestamp' illegible and just appear as one long figure. It needs to be like this because of the application that is imprting the data.

Anyway, the fact is that I need to get ths data into a format that means it is human readable as a timestamp. I don't really want to alter the table as it eeds to be the data type it is in order to receive the data in the first place and this is an ongoing thing.

So, I guess my only option realy is to insert the data from that table into a new table that has a timestamp column of data type 'timestamp'.

How do I do this? I'm pretty sure that I need to 'convert' the data from the string 'timestamp' column so that SQL will not give an error when trying to import it into the timestamp 'timestamp' column in the new table. What is it I need to do?

Thankyou very much

jun0
Starting Member

United Kingdom
32 Posts

Posted - 03/15/2013 :  11:46:27  Show Profile  Reply with Quote
By the way, this is the format of a sample of the timestamps:

1130313182327000
1130313182327000
1130313192812000
1130313200312000
1130313200312000
1130313223343000
1130313224843000
1130313223343000
1130313224843000
1130313230328000
1130313230328000
1130313230343000
1130313232813000
1130314013828000
1130314021329000
1130314022829000
1130314041829000
1130314055845000
1130314055845000
1130314065330000
1130314071345000
1130314071830000
1130314074330000
1130314083845000
1130314081830000
1130314085300000


anyone recognise this format? know how it can be made human readable?
not sure if this makes much sense once it is out of the applicaton that is sending it.

Edited by - jun0 on 03/15/2013 11:59:48
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 03/16/2013 :  08:56:15  Show Profile  Reply with Quote
nobody?
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
418 Posts

Posted - 03/16/2013 :  12:16:01  Show Profile  Reply with Quote
hi

1130314085300000

first character 1= i don't know
2,3 characters =13 i guess year like 2013
4,5 =03 the month
5,6 chars=14 the day

085300 something like 08:53:00

and the last characters 0000 miliseconds maybe


S


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 03/17/2013 :  10:41:41  Show Profile  Reply with Quote
Thanks for the reply stepson.

Can ayone help me with my inital question/issue? ie:

quote:


So, I guess my only option realy is to insert the data from that table into a new table that has a timestamp column of data type 'timestamp'.

How do I do this? I'm pretty sure that I need to 'convert' the data from the string 'timestamp' column so that SQL will not give an error when trying to import it into the timestamp 'timestamp' column in the new table. What is it I need to do?
How do I insert it into a timestamp column andhave the data actually readable as a timestamp? I have managed to insert into a timestamp column but the data is still not legible/looksthe same. Perhaps this is obvious, should importng into another column of a different data type even change the way that the data looks? or is my problem something to do with the initial import..... thanks!

Edited by - jun0 on 03/17/2013 10:42:22
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.05 seconds. Powered By: Snitz Forums 2000