topic Converting Julian Date and Time in QlikView in QlikView

Converting Julian Date and Time in QlikView

mccook — Thu, 17 Jul 2014 15:05:44 GMT

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 — Thu, 17 Jul 2014 15:26:25 GMT

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