Bad Date Output

[#1]

Can anyone explain what it happening when I concatenate two date fields together.

The dates are coming from the same table.  Here's what I'm doing with the first

date(date#(DateField1,'MMMYY'),'M/1/YYYY') as InvoiceDate

I get the results that I want.  It converts JAN15, FEB15, MAR15, etc... to 1/1/2015, 2/1/2015, 3/1/2015, etc...

The conversion I'm doing for the second date is the standard date conversion

Date(DateField2) as InvoiceDate which outputs a list of dates with the same format above but it's a date for every day

1/1/2015

1/2/2015

1/3/2015

etc...  

When I concatenate the two together, the results come out like this.  No individual dates, just a duplicate list of dates for the first day of the month

1/1/2015

1/1/2015

1/1/2015

1/1/2015

2/1/2015

2/1/2015

2/1/2015

3/1/2015

3/1/2015

3/1/2015

etc.....

Does any have a clue as to what is happening and how to fix this?

[MK_QSL]

Use

=MonthStart(date#(DateField1,'MMMYY'))

[jonathandienst · Accepted solution]

Manish's suggestion should work, but if not I would change to this:

date(date#(DateField1,'MMMYY'),'M/D/YYYY')

[giakoum]

date(date#(DateField1,'MMMYY'),'M/1/YYYY') as InvoiceDate

change 1 to D :

date(date#(DateField1,'MMMYY'),'M/D/YYYY') as InvoiceDate

[marcus_sommer]

I would do the second load first and/or do the same formatting like in the first load. Qlikview applied to the whole field the format from first record. You will find more informations here: How to use - Master-Calendar and Date-Values.

- Marcus