bad date format for pivot

together

I can not figure out why in PowerPivot or Excel a Microsoft query does not provide the date fields in the format for Excel as they are necessary. I have already gotten from you in another thread the approach, as in Microsoft Query the fields are output directly as a date. I use the following query to put the records into Excel as a table:

select to_char(DB_Gen.STRT, 'DD.MM.YYYY') "Date" FROM XXX

So I get the data in the format “DD.MM.YYYY”.

So I connected this date column to the automatic calendar table in PowerPivot, but somehow the link does not work. The pivot table can not work with it.

Where exactly is the error? Why is not the field accepted in date format?

How can I find out the error?

Edit: Somehow, the table created from the query does not get the date format. Although the data is displayed as “DD.MM.YYYY”, the column does not have the date format. How can this be changed?

Best Regards Joshua

Answer

Check if the column DB_Gen.STRT is of date type, if so just fetch the data as below

select DB_Gen.STRT "Date" FROM XXX

If the column data type is char, then use TO_DATE to convert it to date type

select TO_DATE(TO_CHAR(DB_Gen.STRT, 'MM/DD/YYYY'), 'MM/DD/YYYY') "Date" FROM XXX