topic Re: Finicky Dates in QlikView

Finicky Dates

Thu, 26 Mar 2015 21:24:09 GMT

I am pulling date data in from a SQL query and need it to be in MM/DD/YYYY, but it stays in SQL's default YYYY-MM-DD

I have tried:

date(DDATE)

date(DDATE, 'MM/DD/YYYY')

date(DDATE, 'MM-DD-YYYY')

date(date#(DDATE, 'YYYY-MM-DD'))

date(date#(DDATE, 'YYYYMMDD'))

To add to the craziness is that the line before this I have: date(DDATE)&Market as key, and the result is in MM/DD/YYYY

Any help would be awesome

Re: Finicky Dates

Ralf-Narfeldt — Fri, 27 Mar 2015 08:10:03 GMT

Where are you doing the date transformations in the script, in the SQL SELECT statement or a preceding LOAD statement?

The preferred way is to do it in the LOAD, transformations in the SQL SELECT are processed by the SQL database which may or may not support them.

An example of your script would help.

Re: Finicky Dates

robert_mika — Fri, 27 Mar 2015 08:26:15 GMT

What are the outcomes of your transformation?

Are the numbers or Text?

Re: Finicky Dates

Anonymous — Fri, 27 Mar 2015 08:33:12 GMT

Can you post a sample qvw

Re: Finicky Dates

Fri, 27 Mar 2015 15:40:49 GMT

Qtable:

SQL

SELECT

DDATE,

Market

FROM Table1;

Merge:

NoConcatenate

LOAD date(DDATE)&Market as key,

date(DDATE) as DDATE

Resident Qtable;

date(DDATE) as DDATE has been replaced with all of the before mentioned variations without any success. The results all look like 2015-02-01

date(DDATE)&Market as key, produces results like 02/01/2015City

Re: Finicky Dates

lawrenceiow — Fri, 27 Mar 2015 16:08:41 GMT

Did you try date(date#(DDATE,'YYYY-MM-DD'),'MM-DD-YYYY')?

The date#() specifies how the date is and the date() specifies how you would like it to be.

Re: Finicky Dates

Fri, 27 Mar 2015 16:10:24 GMT

Yes I did. It did not produce the expected result.

Re: Finicky Dates

awhitfield — Fri, 27 Mar 2015 16:16:22 GMT

Hi Jacob,

what data type is your date field in SQL please?

Andy

Re: Finicky Dates

awhitfield — Fri, 27 Mar 2015 16:23:05 GMT

Why not convert it into the correct format in SQL?

E.g.

select Convert(VarChar(10), Your_Date_Field,101) from your_table

Andy

Re: Finicky Dates

Anonymous — Fri, 27 Mar 2015 16:24:53 GMT

What do you have here:

SET DateFormat=

Re: Finicky Dates

Fri, 27 Mar 2015 20:58:37 GMT

date(num(DDATE) as DDATE produces the correct result as well.