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, 2016 at 22:06
  • are your ETAdate and ETATime values bigints?
    – pacreely
    Dec 12, 2016 at 23:59
  • Bob, on the line that is throwing the error, ETADate is 20161102, and ETATime is 50. Dec 13, 2016 at 15:35
  • pacreely, they are just ints. Dec 13, 2016 at 15:36

1 Answer 1


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


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, 2016 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!! Dec 13, 2016 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, 2016 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, 2016 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.