• Date
  • Date 15 Aug 2019 11:53
  • Replies 1 reply
  • Subscribers 129 subscribers
  • Views 168 views

Dealing with Dates using Excel XDS

In Excel, I can format a date as YYYY-MM-DD, however, when it is ingested into LiveCompare, it changes to (M)M/(D)D/YYYY. I've searched high and low in the Mr Excel sites of the world for how to get my Excel doc to force the YYYY-MM-DD or how to convert in SQLite from (M)M/(D)D/YYYY to YYYY-MM-DD, but everything comes up empty for me.

I was really close with doing a split of the string on / but some of the dates are 1/2/2019 for example and I dont get 2 chars for MM or DD. Long story longer, wondering if anyone knows a way to force LC to read in dates as YYYY-MM-DD from excel OR if anyone has a solution in SQLite to convert that date.

The reason I'm forced into using the YYYY-MM-DD is because all my other dates from ABAP systems, HANA, Java Netweaver are all in that format and I'm doing a Last Logon workflow so I need them to all be the same.

Thanks for reading,
Chris

  • This is because the actual DATE value is stored as a double (think floating point number) representing the number of days from some epoc. The fractional part of the value is used to represent the time of day.

    When this data is read into LiveCompare it will be stored in the embedded database as a string. LiveCompare converts the raw floating point value into a string using the locale information of the LiveCompare server. Windows has options to set the short and long formats of dates in a locale dependent way.

    What that means for you is if the date must then be presented in LiveCompare as YYYY-MM-DD then you will need to reformat it using an ExecuteSQL action.

    Let's say I have a table in LiveCompare (after a Read External Data Source) that contains:
    23/02/2020
    24/02/2020

    and I want to reformat this as:
    2020-02-23
    2020-20-24

    Assume the column name is DATE. This SQL will reformat it:

    CREATE TABLE Result AS
    SELECT
    substr(DATE, 7, 4) || '-' || substr(DATE, 4, 2) || '-' || substr(DATE,1, 2) AS DATE,
    TALLY
    FROM
    Source1