0

I have a TimeStamp (varchar(50),null) column in my SQL Server 2008 table which looks misleading by the name TimeStamp. I mean it appears as if it's a datatype timestamp but it's varchar.

But it has values like 201403240004 which looks like a date. Can I convert it into date and use?

Read online that timestamp is only a sequence of numbers and has nothing to do with date and time.

Community
  • 1
  • 1
rocky
  • 435
  • 3
  • 8
  • 18

2 Answers2

3

You can.

Providing that the format is YYYYMMDDHHmm, a simple way to do that would be:

SELECT CONVERT(DATETIME,
   SUBSTRING([TimeStamp],1,4)+'-'+SUBSTRING([TimeStamp],5,2)+'-'
  +SUBSTRING([TimeStamp],7,2)+' '+SUBSTRING([TimeStamp],9,2)+':'
  +SUBSTRING([TimeStamp],11,2)+':00.000')
FROM Table

This will take this "timestamp" and first transform it to SQL-readable datetime string, i.e. for your example it would be 2014-03-24 00:04:00.000. Then, it will be easily converted to datetime.

Tomas Pastircak
  • 2,867
  • 16
  • 28
2

Yes, your column should be convertible to DATETIME, but you may have to do the converison yourself if CONVERT() does support the format.

I can't tell from you example what the time format really is.

If it is YYYYMMDDHHMM them

SELECT CONVERT(DATETIME,LEFT('201403240004',8),112)
      +CONVERT(DATETIME,SUBSTRING('201403240004',9,2)+ ':' + RIGHT('201403240004',2)+':00' ,108)
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49