Forum Discussion

Mark's avatar
Mark
New Contributor II
4 years ago

Excel Formatter - need date datatype with this format MM/DD/YYYY

Im using Excel Formatter. I am selecting from Oracle. I need the column in excel to be date datatype, but have this format MM/DD/YYYY with no time on the end.

I can get the date into that format if I do a to_char(mydate, ‘MM/DD/YYYY’) in the select, but the datatype is always General no matter if I click the “Translate Date and Time Types”. If I let it be a date in the select as it is naturally from the table datatype or I do a to_date(to_char(mydate, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’), no matter what the checkbox is set to its always General and it puts the time on the end? I cant win.

When I export from Oracle SQL developer to excel, that tool gives date datatype with this format MM/DD/YYYY no problem.

excel

3 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    4 years ago

    @Mark

    Try to select the date as string from Oracle, and then parse it in SnapLogic as date before Excel Formatter. In Excel Formatter Snap check the “Translate date and time types”.

    I am also attaching sample pipeline:

    Comm_2022_03_09.slp (7.5 KB)

    Hope this will helps.

    BR,
    Spiro Taleski

  • Mark's avatar
    Mark
    New Contributor II
    4 years ago

    This doesnt work. The Excel Formatter snap does not allow you to change the datatype of a column no matter what datatype you send it. The Excel formatter always defaults to General. I used this Oracle PL/SQL package to generate a Excel document and this package kind of has the same issue where it defaults to “Custom” but for our purposes the API likes Custom and Date datatypes equally when a date is concerned, but doesnt like General. Dont know why. Recommend, somehow, allowing the Excel Formatter snap to change column datatypes. I dont need a response to this unless you have a fix where I dont have to use PL/SQL.