Power bi export to CSV adds the time

[hammie]

 On Power Bi, my date column has the form of mm/dd/yyyy however when exported to CSV, it adds the time. How can I fix it so it only shows mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss? 

[v-juanli-msft · Accepted solution]

Hi @hammie

After testing, I face the same problem with you.

A workaround I use is format the measure to a text value as"mm/dd/yyyy "

Look at the following picture, measure3 is a measure calculated from column[date] and [column], it is a date/time value.

The measure 5 converts the measure3 from date/time format to text format.

Then export data and open in notepad

Measure 5 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Measure 3]),"/"),DAY([Measure 3])),"/"),YEAR([Measure 3]))

Best Regards

Maggie

[RMDNA]

@hammie,

Make sure your date column type is set to mm/dd/yyyy in the Query Editor.

[hammie]

@RMDNA Thank you but here is more info

this column is a measure I have created by using 2 different date columns. One column is mm/dd/yyyy and the other column I used to caluculate is mm/dd/yyyy HH:MM;SS. however i changed my result column format as mm/dd/yyyy and it shows as so. However when I export it to CSV, the result column is shown as mm/dd/yyyy HH;MM;SSS. But I don't want the time, just the date to stay on the CSV.

[ChrisMendoza]

@hammie,

Date & Time is really just a decimal and what you see in Power BI is a Formatted version of that number.

I'm guessing your export returns something like: 

DATEonly,TIMEonly,DATEandTIME
2018-06-11 00:00:00,1899-12-31 16:53:00,2018-06-11 16:54:12

I'm curious to know how you are returning the values as a Measure as I am getting an error.

I was able to create a Calculated Column as:

Column = 
VAR yr = YEAR(Table1[DATEandTIME])
VAR mo = MONTH(Table1[DATEandTIME])
VAR day = DAY(Table1[DATEandTIME])

VAR YrMoONLY = COMBINEVALUES("-",yr,mo,day)

RETURN YrMoONLY

which returns a TEXT verison that exports as:

 Of course, then when you re-import that 'number' 2018-06-11' you'll have to correct back into time format if it is required.