Forum Discussion

trevb's avatar
trevb
Advocate II
9 years ago

Datevalue incorrect conversion

Blast, just realised what my issue was here!   I have a date in english form that is stored in a Parameters table so I can access it from PBI Desktop.  The original parameter is A date in power que...
AlexChen's avatar
AlexChen
Microsoft Employee
9 years ago

Hi,

 

I think you can reference article https://msdn.microsoft.com/en-us/library/ee634543.aspx.
Please see content below from this article.

 

The DATEVALUE function uses the locale and date/time settings of the client computer to understand the text value when performing the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", would be converted to a datetime value equivalent to January 8th of 2009. However, if the current date and time settings represent dates in the format of Day/Month/Year, the same string would be converted as a datetime value equivalent to August 1st of 2009.

 

If you want to make DateValue(“1/8/2009”) equals “Augurest 1st 2009”. You have to change the date format setting on local machine. Please follow the steps below.

 

1.  Go to control panel and click “Date and Time”, choose “Change date and time”.

 

 

2.  Click link “Change calendar settings”.

 

 

3. Change “Short date” value to “d/M/yyyy”, just like below.  Click “Apply” and “OK”.

 

 

4. See test result below:

 

 

 

Best Regards

Alex