SQL Server – DateTime Conversion Out-of-Range Error for Calculated Value

datetimesql servertype conversion

So I keep getting an error and I have no clue what is prompting it. I have the SQL

STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' ')

Which produces on the problem line the value :

'20161102 00:50:00'

When I call the following SQL, the DateTime conversion works just fine.

CONVERT(DATETIME, '20161102 00:50:00')

But when I try to put both together with the following:

CONVERT(DATETIME, STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' '))

Then and only then, I get the error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Does anyone here have any clue as to why the my nvarchar to datetime conversion works when I manually supply a string but fails when I try to convert from math that generates the exact same string? Any insight anyone has would be great because as of now this makes absolutely no sense.

Best Answer

Are you able to use date based functions to build your datetime.

Select
Dateadd(hh,11,
Dateadd(mi,50,
Dateadd(ss,30,
Convert(datetime,eatdate))))

Sorry if I've made any mistakes, I'm typing on my phone.

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

SQL Server – How to Convert CHAR to DATETIME for DATEADD

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;

Best Answer

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

SQL Server – How to Convert CHAR to DATETIME for DATEADD

Related Question