SQL Date-Time Conversion with nested converts experiences random conversion errors

Question

Looking for assistance with a strange issue if anyone has ideas:

I have a SQL that statement works most of the time in a T-SQL script but crashes occasionally. I have identified the data that a crash occurs on and cannot identify any difference between data rows that work.

The goal of this code is to add the time to an already existing datetime value that has 00:00:00 as the time from the second time column (as outlined below). My goal is to combine both columns into YYYY-MM-DD HH:MM:SS format, but I had to convert them to char first to trim off the orignal 00:00:00.

Columns

LogDate - contains date only in DateTime format (YYYY-MM-DD HH:MM:SS)

LogTime - contains the time of the action and is in varchar format (HH:MM)

SQL Conversion

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), LogDate, 112) + ' ' + CONVERT(CHAR(8), LogTime, 108))
                FROM  TestTable
                WHERE EventSerial = '100001'

However, if I change the EventSerial in the above statement to a different row, such as '100002', the statement works.

The data for each row is below:

EventSerial 100001's values: LogDate: 2015-04-02 00:00:00.000 LogTime: 10:04

EventSerial 100002's values: LogDate: 2015-04-02 00:00:00.000 LogTime: 10:48

Running with data set 1 fails, running with data set 2 produces output. Also, running the code without the final datetime conversion works, or if I run the code with the string manually it works (as outlined below:)

SELECT CONVERT(CHAR(8), LogDate, 112) + ' ' + CONVERT(CHAR(8), LogTime, 108)
                FROM  TestTable
                WHERE EventSerial = '100001'


SELECT  CONVERT(DATETIME, '20150402 10:48')
SELECT  CONVERT(DATETIME, '20150402 10:04')

Any suggestions, I'm sure its something silly that I'm missing (and I probably took the long way around the issue anyway. The desired output would be 2015-04-02 10:04:00

must be something wrong with your data. [I can't reproduce the error.](http://sqlfiddle.com/#!3/6d156/9) This would be avoided if you used the proper data types. — Zohar Peled, Jun 03 '15 at 21:25
Completely agree, unfortunately the database datatypes and schema was defined long before I was here (and can't be changed unfortunately). Thanks for attempting to reproduce it, I appreciate it! — sallou, Jun 04 '15 at 14:16
Try to update the value of LogTime in the problematic row, perhaps there is a hidden character there that is causing all this problem. — Zohar Peled, Jun 04 '15 at 14:19

score 0 · Accepted Answer · edited May 23 '17 at 12:06

First, datetime has no format. (why?)

Second, you don't need to convert the datetime value to char to add hours and minutes, just use DateAdd:

SELECT DATEADD(Minute, 
               CAST(RIGHT(LogTime, 2) as int), 
               DATEADD(Hour, 
                       CAST(LEFT(LogTime, 2) as int), 
                       LogDate
                   )
              )
FROM  TestTable
WHERE EventSerial = '100001'

Also, note that convert does not hold a style for yyyymmdd hh:mm

Note: code was written directly here, there might be some mistakes.

Brian Pressler · Answer 2 · 2015-06-04T16:53:51.113

0

I'm not sure why you're getting the error... possibly there are some unseen characters in your varchar time field... like a tab or something maybe? Try this query:

SELECT ascii(substring(LogTime,1,1)) Char1, 
    ascii(substring(LogTime,2,1)) Char2,  
    ascii(substring(LogTime,3,1)) Char3,  
    ascii(substring(LogTime,4,1)) Char4,  
    ascii(substring(LogTime,5,1)) Char5
FROM  TestTable
WHERE EventSerial = '100001'

It should show these results:

Char1       Char2       Char3       Char4       Char5
----------- ----------- ----------- ----------- -----------
49          48          58          48          52

(1 row(s) affected)

This would be a bit more efficient:

select dateadd(minute, datediff(minute,0, LogTime), LogDate)
FROM  TestTable

But this assumes that your date field always has 00:00:00 time information. If you want to be sure that is stripped out as well you could use:

select dateadd(minute, datediff(minute,0, LogTime), dateadd(day, datediff(day, 0, Logdate),0))
FROM  TestTable

edited Jun 04 '15 at 16:53

answered Jun 03 '15 at 18:43

Brian Pressler

6,653
2
19
40

Interesting. I tried both of these methods, and I still receive the same error with the same variation (EventSerial2 data works, EventSerial1 data does not). In fact, If I mix up the data in any form or fasion, the common demoniator appears to be the LogTime field. However, I checked it in a hex editor and it is literally 10:04, and I did a (LEN) on it and I get 5 returned. – sallou Jun 03 '15 at 19:50
Is it possible that one of the zeros is a letter O instead? – Brian Pressler Jun 03 '15 at 19:58
Double checked and they are in fact numeric 0's and not O's unfortunately. Good suggestion though! – sallou Jun 04 '15 at 14:16
Mystifying. Try running the query I added to my answer. – Brian Pressler Jun 04 '15 at 16:54

SQL Date-Time Conversion with nested converts experiences random conversion errors

2 Answers2