Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

afuchten
Valued Contributor

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

am I missing something??

I have a date field which I load from a CSV file ... in the format M\D\YYYY e.g. 3/10./2010
I want to convert this text field into a date field with the format DD-MM-YYYY.

In the load script I do the following:


...
date(date#(DateReady,'M\D\YYYY')) as Combi.Date,
...


The result is an empty date ...

when only doing

date#(DateReady,'M\D\YYYY')


I get the resulting 3/10/2010

What am I doing wrong?? In other cases we did the same thing and that seems to work.

Anita

1 Solution

Accepted Solutions
sparur
Valued Contributor II

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

Hello, Anita

what format in your source?

MM\DD\YYYY e.g. 3\10\2010

or

MM/DD/YYYY e.g. 3/10./2010

?

for first, try: Date(date#(DateReady,'MM\DD\YYYY'), 'DD-MM-YYYY') as DateReady

for second, Date(date#(DateReady,'MM/DD/YYYY'), 'DD-MM-YYYY') as DateReady

7 Replies
sparur
Valued Contributor II

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

Hello, Anita

what format in your source?

MM\DD\YYYY e.g. 3\10\2010

or

MM/DD/YYYY e.g. 3/10./2010

?

for first, try: Date(date#(DateReady,'MM\DD\YYYY'), 'DD-MM-YYYY') as DateReady

for second, Date(date#(DateReady,'MM/DD/YYYY'), 'DD-MM-YYYY') as DateReady

Not applicable

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

Hi Anita,

Try following dimensions which solve your qurey.

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

Best,

DP

afuchten
Valued Contributor

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

This results in an empty value ... so doesn't work.

Did try a lot of different options but none of them seems to work 😞

Highlighted
afuchten
Valued Contributor

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

mmm strangely the MM/DD/YYYY thing worked ...

Strange that the other formatting did work half way ...

can't explain why

date(date#(Combi.DatumKlaargezet,'M\D\YYYY')) as Combi.Datum1

results in a half way manner ...

But thx ... can continue on this :D!!

sparur
Valued Contributor II

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

so, try to parse fields manually:

LOAD

date(Makedate(year, month, day), 'DD-MM-YYYY') as FormatDate

;

LOAD

DateReady,

Left( DateReady, 2) as month,

mid( DateReady, 4,2) as day,

right( DateReady, 4) as year

FROM....

sparur
Valued Contributor II

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

what are the date format in your source data?

can you give some of rows from your file with dates?

convert text M\D\YYYY to date field DD-MM-YYYY -- am I missing something??

hi try this

Date(date#(Sdate,'MM\DD\YYYY') as SourceDate