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?

Improve this question
1

1 Answer 1

Reset to default
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)
Improve this answer
7
  • 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
    Commented Oct 26, 2015 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
    Commented Oct 26, 2015 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
    Commented Oct 26, 2015 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
    Commented Oct 26, 2015 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
    Commented Oct 26, 2015 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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