Skip to main content
A
AndreaF
Expert
March 27, 2023
Solved

Dates format issue with LoadCustomTableUsingDelimitedFile function

  • March 27, 2023
  • 1 reply
  • 0 views

Hi,

I have an application DB table with a date column and I am using the LoadCustomTableUsingDelimitedFile function to load a CSV file to the table. The xfDateTime# token is assigned to the column in the LoadCustomTableUsingDelimitedFile  function.

The dates in the CSV file are in the dd-mm-yyyy format. However, the LoadCustomTableUsingDelimitedFile function assume the dates are in the mm-dd-yyyy format. Data is loaded but when querying the table using the Month() SQL function for example, the day is return instead.

Is there a way to make sure LoadCustomTableUsingDelimitedFile  knows the date column in the data file is in the dd-mm-yyyy format?

Thank you

Best answer by AndreaF

The workaround I have come up with is to recreate the application table by making sure the date column is in fact of varchar type. Then I have updated the LoadCustomTableUsingDelimitedFile function to use the xfText# token for that column. When querying the table, for example to insert the value to a date column of another table, I am using the "CONVERT(datetime, ColumnName, 105)" SQL function, where the 105 represent the date style dd-mm-yyyy.

1 reply

A
AndreaFAuthorAnswer
Expert
March 28, 2023

The workaround I have come up with is to recreate the application table by making sure the date column is in fact of varchar type. Then I have updated the LoadCustomTableUsingDelimitedFile function to use the xfText# token for that column. When querying the table, for example to insert the value to a date column of another table, I am using the "CONVERT(datetime, ColumnName, 105)" SQL function, where the 105 represent the date style dd-mm-yyyy.

J
OneStream Employee
JackLacavaOneStream Employee
OneStream Employee
March 28, 2023

I suspect another workaround would be to obtain a sessionInfo object with culture settings different from the default (US), and then pass that to the function.

A
AndreaFAuthor
Expert
March 28, 2023

Hi, thank you for your reply. As far as I can see the LoadCustomTableUsingDelimitedFile function does not have a parameter for the culture setting, so I cannot pass that to the function.

AndreaF_0-1680023410776.png

 

Terms & ConditionsAccessibility statement