Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
lawrenceiow
Creator II
Creator II

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.

View solution in original post

10 Replies
Ralf-Narfeldt
Employee
Employee

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.

robert_mika
Master III
Master III

What are the outcomes of your transformation?

Are the numbers or Text?

Anonymous
Not applicable
Author

Can you post a sample qvw

Not applicable
Author

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
Creator II
Creator II

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
Author

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

awhitfield
Partner - Champion
Partner - Champion

Hi Jacob,

what data type is your date field in SQL please?

Andy

awhitfield
Partner - Champion
Partner - Champion

Why not convert it into the correct format in SQL?

E.g.

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

Andy

Anonymous
Not applicable
Author

What do you have here:

SET DateFormat=