Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Finicky Dates

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

Tags (2)
1 Solution

Accepted Solutions
lawrenceiow
Contributor II

Re: Finicky Dates

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.

10 Replies
Employee
Employee

Re: Finicky Dates

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

What are the outcomes of your transformation?

Are the numbers or Text?

bhagirath
Contributor III

Re: Finicky Dates

Can you post a sample qvw

Not applicable

Re: Finicky Dates

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

lawrenceiow
Contributor II

Re: Finicky Dates

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.

Not applicable

Re: Finicky Dates

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

awhitfield
Esteemed Contributor

Re: Finicky Dates

Hi Jacob,

what data type is your date field in SQL please?

Andy

awhitfield
Esteemed Contributor

Re: Finicky Dates

Why not convert it into the correct format in SQL?

E.g.

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

Andy

mov
Esteemed Contributor III

Re: Finicky Dates

What do you have here:

SET DateFormat=

Community Browser