6

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.

  • Can you post your data tabel and your SQL please? – Andrew Paes Sep 10 '15 at 19:00
  • 7
    If you have the possibility of runningINSERT INTO table_name (DateColumn) VALUES ('Bacon); you've probably selected the wrong datatype. – Zane Sep 10 '15 at 19:12
  • I cant agree with you enough Zane, I will admit that made me laugh and crave bacon for a moment. – Hector Sep 10 '15 at 19:15
13

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;
  • 1
    Date is stored in a Date_stamp (char (8), not null) I agree 100% Aaron, we purchased a 3rd party app to handle particular transactions in the plant and sure enough the db design is very poor for the exact reasons you just mentioned. We dump the data from a oracle DB using SP. Luckly there is no direct entry fields to allow someone to substitute 5pm with foobar, but I now have to come here to have someone surgically implant a dateadd function for me. Thanks for the speedy reply – Hector Sep 10 '15 at 19:11

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.