CODEWITHSUNDEEP
sqlsql-server
RanchiRhino
RanchiRhino

Reputation: 774

SQL Server Date time conversion giving incorrect result?

I have 6 digit value in one column that I need to convert to date-time. I tried two different formula given below.

  1. (DATEADD(day, CONVERT(int, COLUMNNAME)-((1000*(CONVERT(int, COLUMNNAME)/100)))-1, DATEADD(year, CONVERT(int, COLUMNNAME/1000), '1 Jan 1900'))) as Order_date

But this is giving following error message:-

Adding a value to a 'datetime' column caused an overflow. [SQLSTATE=22007, SQLERRORCODE=517]

  1. convert(datetime, (convert (int, COLUMNNAME)), 6) as Order_date

And this is giving incorrect value for date. There is one particular value 118150 that should result into 2018-05-30 :00:00:00, but my statement is returning 2223-06-27 00:00:00

Can anybody please help what is causing error with first statement and how can I modify it to run on entire table.

Upvotes: 2

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If I understand the date format correctly, this will work:

select dateadd(day, x % 1000 - 1, datefromparts(1900 + x / 1000, 1, 1))
from (values (118150)) v(x)

Upvotes: 4

Related Questions