Converting Julian Date and Time in QlikView

[mccook]

Hi,

If I have a julian date that I need to convert in QlikView i use something like this:

Date(MakeDate(1900+left(text(FIELDNAME),len(FIELDNAME)-3))+right(text(FIELDNAME),3)-1) as [Date],

But I'm not sure how to convert a field that is Date and Time.

Can you help please?

Cheers,

Dean

[hic · Accepted solution]

What does your field look like? Is the time just the decimal part, so that Jan 3, 2014 12:00 is written as 2014003.5 ?

If so, I would suggest

     Floor(FIELDNAME / 1000) as Year,

     1000 * Frac(FIELDNAME / 1000) as DayAndTime,

     TimeStamp( MakeDate( Floor(FIELDNAME / 1000) ) + 1000 * Frac(FIELDNAME / 1000) -1 ) as Timestamp,

HIC