Finicky Dates

[#1]

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

[Ralf-Narfeldt]

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]

What are the outcomes of your transformation?

Are the numbers or Text?

[Anonymous]

Can you post a sample qvw

[#5]

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 · Accepted solution]

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.

[#7]

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

[awhitfield]

Hi Jacob,

what data type is your date field in SQL please?

Andy

[awhitfield]

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]

What do you have here:

SET DateFormat=