2

I have to develop system which is accept data (in database) from another system. The problem I face is all DATE and TIME data is in NVARCHAR. I have to convert to DATE and TIME in order to calculate the duration. I am using SQL Server 2008 R2.

Data example:

STR_YMD     STR_HMS
--------------------
20150101    151000
20090807    123009
20130113    145602
20090515    061700

How could I convert this data into following DATETIME data?

STR_DT 
--------------------
2015-01-01  15:10:00
2009-08-07  12:30:09
2013-01-13  14:56:02
2009-05-15  06:17:00

Hopefully someone could help me. Thanks in advance

Bet

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bet
  • 21
  • 1

1 Answers1

2

Something like this ?

SELECT CAST(SUBSTRING(STR_YMD, 1,  4) + '/' + SUBSTRING(STR_YMD, 5,  2) + '/' +
SUBSTRING(STR_YMD, 7,  2) + ' ' + SUBSTRING(STR_HMS, 1,  2) + ':' + 
SUBSTRING(STR_HMS, 3,  2) + ':' + SUBSTRING(STR_HMS, 5,  2) AS DateTime)

EDIT : You can use LEFT function to be compatible with short time formats : 

SELECT CAST(SUBSTRING(STR_YMD, 1, 4) + '/' + SUBSTRING(STR_YMD, 5, 2) + '/' +
SUBSTRING(STR_YMD, 7, 2) + ' ' + SUBSTRING(LEFT(STR_HMS+'000000', 6), 1, 2) + ':' +
SUBSTRING(LEFT(STR_HMS+'000000', 6), 3, 2) + ':' +
SUBSTRING(LEFT(STR_HMS+'000000', 6), 5, 2) AS DateTime)
stephanejulien
  • 177
  • 13
  • It is possible but the length of the STR_HMS might be various so i have to check all variant. e.g. 1750. I think there is some convert function that i could use. Thanks anyway. – Bet Jul 13 '15 at 07:37
  • Hi @Bet if this answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – stephanejulien Aug 06 '15 at 14:14