topic Re: Unable to override default date format in load script in QlikView

Unable to override default date format in load script

swati_rastogi27 — Fri, 09 Sep 2016 07:22:16 GMT

I want the date format to be 'DD-MMM-YYYY' , but no matter what conversion i apply , I am unable to change it.

The format that appears is 'M/D/YYYY' along with timestamp 12:00:00

I tried DATE(DATA_DATE,'DD-MMM-YYYY')AS DATA_DATE in the load script

I also tried to set default date format

SET DateFormat='DD-MMM-YYYY';

None of this seems to work

Re: Unable to override default date format in load script

arulsettu — Fri, 09 Sep 2016 07:25:52 GMT

try this

date(Date#(datefield,'D/M/YYY hh:mm:ss tt'),'DD-MMM-YYYY')

Re: Unable to override default date format in load script

Anonymous — Fri, 09 Sep 2016 07:33:09 GMT

or may be something like this?

DATE(FLOOR(Date#(DateField,'DD-MMM-YYYY')),'DD-MMM-YYYY') AS NEWDATE

What is your Data source?

Re: Unable to override default date format in load script

sunny_talwar — Fri, 09 Sep 2016 07:34:49 GMT

How about this:

Date(Floor(DATA_DATE)) as DATA_DATE

Re: Unable to override default date format in load script

swati_rastogi27 — Fri, 09 Sep 2016 07:37:36 GMT

My data source is Oracle where the standard format is defined as 'DD-MMM-YYYY'

Re: Unable to override default date format in load script

swati_rastogi27 — Fri, 09 Sep 2016 07:42:06 GMT

Using Date# gives null result

Re: Unable to override default date format in load script

Anonymous — Fri, 09 Sep 2016 07:45:14 GMT

then try timestamp#() instead of Date#()

Re: Unable to override default date format in load script

swati_rastogi27 — Fri, 09 Sep 2016 10:37:16 GMT

1. I picked up DATA_DATE from Oracle, stored in a TEMP table

2. Did resident load on TEMP table , changing the date format

date(DATA_DATE,'DD-MMM-YYYY') AS DATA_DATE

3. Stored in QVD

When I read from this QVD, again the format in my table box appears as M/D/YYYY 12:00:00 AM

Re: Unable to override default date format in load script

t_moutault — Fri, 09 Sep 2016 10:49:29 GMT

hi,

try this :

Date(Date#(SubField(DATEFIELD,' ',1),'D/MM/YYYY'),'DD/MMM/YYYY')

Date(timestamp#('1/12/2016 10:35:21 AM','D/MM/YYYY hh:mm:ss tt'),'DD/MMM/YYYY')

Re: Unable to override default date format in load script

Anil_Babu_Samineni — Fri, 09 Sep 2016 11:14:08 GMT

Swati, You are saying you are fetching from qvd - So, Would you be able to provide sample

1. I picked up DATA_DATE from Oracle, stored in a TEMP table

2. Did resident load on TEMP table , changing the date format

date(DATA_DATE,'DD-MMM-YYYY') AS DATA_DATE

3. Stored in QVD

When I read from this QVD, again the format in my table box appears as M/D/YYYY 12:00:00 AM

1) How DATE_DATE looking?

2) date(date#(DATA_DATE,'YourDateFieldFormat'),'Required Format') AS DATA_DATE

Then Try