Converting CHAR to DATETIME so I can use DATEADD()

Question

I have a question regarding using a DATEADD() function to a column that is identified as CHAR (6)

The time_stamp column holds values like 131329 as hhmmss. Whenever I try to create a new column which will hold the value of the time_stamp column + 5 mins, I get an error.

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.`

What I am trying to do is create a new column that is 5 mins ahead of the time_stamp. I am working on a report right now that shows 'completed orders' but I need the report to hold the order as NOT COMPLETED for at least 5 mins before it shows on the report as Completed.

Answer 1

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a single datetime column. Have the "designers" please read this and this.

But barring that (funny how every time you suggest a table change, they say they can't change the table):

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('131329');

SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),STUFF(STUFF(y,5,0,':'),3,0,':'))) 
  FROM @x;

Result:

13:18:29

If you find that query ugly and tedious, good! There's a good reason: You're storing your data wrong.

And this won't be the last problem you have with this design, either. What is stopping someone from inserting 967286 or foobar or <empty string> in your CHAR(6) column? If you need to work around that problem, you can use TRY_CONVERT() in 2012 and up (please always specify version with a version-specific tag), or a CASE expression in earlier versions.

DECLARE @x TABLE(y CHAR(6));

INSERT @x(y) VALUES('foobar');

-- 2012:
SELECT DATEADD(MINUTE,5,TRY_CONVERT(TIME(0), STUFF(STUFF(y,5,0,':'),3,0,':'))) 
  FROM @x;

-- older:
SELECT DATEADD(MINUTE,5,CONVERT(TIME(0),
CASE WHEN ISDATE(STUFF(STUFF(y,5,0,':'),3,0,':'))=1 THEN
    STUFF(STUFF(y,5,0,':'),3,0,':') END))
  FROM @x;