topic Re: Masonic calendar to Standard Date Format in QlikView

Masonic calendar to Standard Date Format

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am fetching data from Oracle DB,where date fields are having values starting from '1/1/4000'.

How can we convert this to Standard Date Format 'DD/MM/YYYY hh:mm:ss TT'.

Chanty4u — Fri, 05 Aug 2016 05:33:50 GMT

try

Date(Date#(urDatefield,'D/M/YYYY'),'DD/MM/YYYY hh:mm:ss TT') AS newdate

Gysbert_Wassenaar — Fri, 05 Aug 2016 06:02:46 GMT

Date(AddYears(Floor(Date#(MasonicDate,'DD/MM/YYYY')),-2000),'DD/MM/YYYY hh:mm:ss TT') AS GregorianDate

If you want to keep the time parts then remove the floor() function from the above expression.

Pulkit_Thukral — Thu, 11 Aug 2016 04:58:08 GMT

Thanks for the reply Gysbert,

Explanation of date field format :-

(1^st octet is month, 2^nd Octet is days and 3^rd Octet is hours).

For eg: 000 8:22:13 (7 months, 21 days, 13 hrs)

This is required to be converted to standard format in minutes i.e(mm)