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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change data type? or strings to timestamp column

Author  Topic 

jun0
Starting Member

32 Posts

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

32 Posts

Posted - 2013-03-15 : 11:46:27
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.
Go to Top of Page

jun0
Starting Member

32 Posts

Posted - 2013-03-16 : 08:56:15
nobody?
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-03-16 : 12:16:01
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

32 Posts

Posted - 2013-03-17 : 10:41:41
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!
Go to Top of Page
   

- Advertisement -