0

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.

  • 2
    can you add some sample data for the ETADate and ETATime values. – Bob Klimes Dec 12 '16 at 22:06
  • are your ETAdate and ETATime values bigints? – pacreely Dec 12 '16 at 23:59
  • Bob, on the line that is throwing the error, ETADate is 20161102, and ETATime is 50. – Connor McAllister Dec 13 '16 at 15:35
  • pacreely, they are just ints. – Connor McAllister Dec 13 '16 at 15:36
-1

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.

  • OP is not building time out of values, just adding the : and space. – Bob Klimes Dec 13 '16 at 15:28
  • Well, your method of converting my stupidly formatted dates and times into actual DateTimes seems to work. I still don't understand why the method I was using was having problems, but the way you've provided works without issue so I'll just use it instead. Thanks!! – Connor McAllister Dec 13 '16 at 15:48
  • I tried your method using int and it failed due to overflow, but it worked when I used bigint. The dangers of multiplying by 10000 – pacreely Dec 13 '16 at 16:59
  • Hi Bob, the problem is with converting the string to datetime. The string build is not the end goal. – pacreely Dec 13 '16 at 17:04

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.