2

I have a question. I am creating a report where the query pulls the Julian Date in iSeries DB2 for AS400.

How can I convert it to Gregorian Date in SSRS. I tried to modify my SQL query but was unsuccessful. So decided to convert it in SSRS.

Let's say I get a number 116193. How do I convert it to July 11 2016. (Eg: 116193+1900000=2016193. 2016 year and 193rd day).

I can make a lot of if statements but I want something easier.

e_i_pi
  • 4,590
  • 4
  • 27
  • 45
Roostam
  • 55
  • 1
  • 1
  • 7

2 Answers2

0

Given: 116193 = Jul 11 2016

with YourTable as (
    select 116193 as juldate
    )

select
   juldate, adjdate, thedate
from YourTable t
cross apply (select t.Juldate+1899999 adjdate) a1
cross apply (
      select dateadd(day,adjdate % 1000,dateadd(year,(adjdate/1000)-1900,0)) thedate
    ) a2

+---------+---------+---------------------+
| juldate | adjdate |       thedate       |
+---------+---------+---------------------+
|  116193 | 2016192 | 11.07.2016 00:00:00 |
+---------+---------+---------------------+

The first cross apply just supplies an alias that can be used in the second apply. it can be done without either if preferred.

select 
dateadd(day,(t.Juldate+1899999) % 1000,dateadd(year,((t.Juldate+1899999)/1000)-1900,0)) thedate
from YourTable t

btw: because a year starts with Jan 1, the date arithmetic requires 1 day less than 193 i.e. if you add 193 days to Jan 1 you would get 12th July.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

IN SQL

DECLARE @JulianDate INT = 116193

SELECT DATEADD(DD, CAST(Right(@JulianDate + 1900000, 3) AS INT) - 1, CAST(LEFT(@JulianDate + 1900000, 4) AS DATE))

IN SSRS,

= DateAdd("d",CInt(Right(Fields!Julian.Value + 1900000, 3)) - 1 , CDate("1/1/"& Left(Fields!Julian.Value + 1900000, 4)))
CuriousKid
  • 605
  • 5
  • 24