SQL Server convert JDE Julian date to char

I love writing an adhoc query for business users or managers.  I sit back and admire the data that I give them.  Generally I do NOTHING to make the data sensible, just give them a big pile of data.  I have learnt over the years, that to save some back and forthing, I tend to provide more data than less…  What?  That’s right, a bit of pro-activity for my end users.

What do I mean?  Simple really.  Include things like “Report descriptions”, “Version Descriptions” and a classic – convert the julian dates for them!  Nice…

So instead of:

select  VRPID, VRVERS, VRVED

FROM ps_pd811.pd811.f983051
where vrpid in (
SELECT t1.siobnm
FROM ps811.ol811.f9860 t1, ps811.ol811.f9861 t2
where simrgmod = 'C'
and t1.siobnm = t2.siobnm
and t2.sipathcd = 'PD811'
and t2.siobnm like 'R%')
and vrved > 111000 ;

Which gives

I did this:

select top 10 VRPID, simd as "Report Description",  VRVERS, VRJD as "Version Description", CONVERT(char(10), DATEADD(day, ((vrved - (CONVERT(int, vrved / 1000) * 1000)) - 1), DATEADD(year, CONVERT(int, vrved / 1000), '1900-01-01')), 121) as "Last run date"
FROM ps_pd811.pd811.f983051, ps811.ol811.f9860 t4
where vrpid = t4.siobnm
and  vrpid in (
SELECT t1.siobnm
FROM ps811.ol811.f9860 t1, ps811.ol811.f9861 t2
where simrgmod = 'C'
and t1.siobnm = t2.siobnm
and t2.sipathcd = 'PD811'
and t2.siobnm like 'R%')
and vrved > 111000 ;

 

The actual information is all custom reports or reports that have been customised and the last time that they were run