-1

I want to convert the julian date in a date format.  Below, please find an example to create my error. All my dates have 5 digits I need to convert.

create table #test (dateR int)

insert into #test (dateR)
values (39596),(39596),(39595),(39595),(39593),(39592),(39592),(39589),(38104),(38104),(37957)

SELECT * from #test

select *
    , dateadd (year, dateR/1000 - 1900, dateR %1000 - 1) as Rd
from #test

Getting the error:

Msg 517, Level 16, State 1, Line 2 Adding a value to a 'datetime' column caused an overflow.

Also tried:

SELECT DATEADD(dd, CONVERT(int, RIGHT(dateR,3)) - 1, CONVERT(datetime,SUBSTRING(dateR,1,2)+'0101', 212))

This returns:

Msg 8116 level 16 state 1 line 1 nullArgument data type int is invalid for argument of substring function

Dale K
  • 25,246
  • 15
  • 42
  • 71
CM2K
  • 833
  • 3
  • 18
  • 38

1 Answers1

3

You can just cast the INT value to a DATETIME.

For example:

SELECT CAST(39596 AS DATETIME); -- Returns 2008-05-30 00:00:00.000
Stijn
  • 1,970
  • 3
  • 27
  • 36
  • now the query runs, but the results i'm getting are not ok. I'm getting 2040-08-18, 2040-08-17, 2040-08-15,2040-08-11 and so on. And if i take this number : 39596 and put in in excel and chose the data type as short date it returns 28.05.2008, and that's exactly how I want to have it. I also found this link and adapted it but it's for 6 digits and in my case yields very bad results http://stackoverflow.com/questions/9671409/converting-jde-julian-date-to-gregorian thoughts? – CM2K Oct 07 '15 at 08:56
  • You can just cast the INT value to a DATETIME value then: `SELECT CAST(39596 AS DATETIME);`. I'll edit my answer. – Stijn Oct 07 '15 at 08:59
  • thank you! this is what i wanted, it was so easy but i couldn't see it. – CM2K Oct 07 '15 at 09:06
  • I have to do this on the server and on the sybase server. In sql server, now works great. Any idea how I could do this in sybase too? getting this error: There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. Explicit conversion from datatype 'INT' to 'DATETIME' is not allowed. Sybase error code=529 . It should work, it's still t-sql. – CM2K Oct 07 '15 at 09:11
  • exactly what i needed, you saved the day. (saw sybase yesterday for the first time) – CM2K Oct 07 '15 at 09:19
  • at first i thought I was going to find the answer there, but it didn't work. So feel free to take a look at this question i opened here: http://stackoverflow.com/questions/32989955/convert-int-to-datetime-in-sybase – CM2K Oct 07 '15 at 10:57