1

I have a varchar column named [Dato Time] which contains values like:

2011-05-12 00
2011-05-12 01
2011-05-12 02

So, a date and a Hour-specific time.

I would like to split the date and the time up in two columns, a column named [Dato] with datatype date and a column named Time with datatype time(7).

I extracted the date-part with the following substring:

UPDATE Dato_Time SET [Dato] = SUBSTRING([Dato Time], 1, 10)

This worked without any problem. But when I try to run the same code for the time-part:

UPDATE Dato_Time SET [Time] = SUBSTRING([Dato Time], 12, 13)

It gives me the following error:

Conversion failed when converting date and/or time from character string.

Any ideas?

2

Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0) 

MSDN: TIMEFROMPARTS (Transact-SQL)

With SQL Server prior to 2012, you can use such queries:

Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)
  • Thanks @Julien Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0) solved the issue for me. No need for the "old" code, as I'm running 2014. But thanks again. – dmh Oct 26 '15 at 9:13
  • 1
    @dmh: Please note also that the third parameter of SUBSTRING is a length, not a position. I'm guessing you want to cut 2 characters at the position of 12 for the time column – therefore the SUBSTRING call should be CAST(SUBSTRING([Dato Time], 12, 2). – Andriy M Oct 26 '15 at 9:38
  • @Andriy, Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0) Gave me this output: 00:00:00.0000000 01:00:00.0000000 etc. Which is fine for me. Although this would have been more than enough: 00:00 01:00 – dmh Oct 26 '15 at 10:02
  • The output is of type Time. Therefore it looks like 00:00:00.000000. You should store it using this type and avoid varchar such as 00:00. – Julien Vavasseur Oct 26 '15 at 10:04
  • 1
    @dmh: Yes, 13 instead of 2 works in this case, because the characters you are extracting are the last two characters of the string – any integer value greater than 2 would give you the last two characters. I'm just pointing out a seeming misconception on your side, because it seemed to me you thought the syntax of SUBSTRING was SUBSTRING(string_value, start_pos, end_pos), while in fact it's SUBSTRING(string_value, start_pos, length). – Andriy M Oct 26 '15 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.