SQL Dataset Inconsistent Date Recognition

neverest
neverest
in Connectors

I'm using tsql to create a dataset in Workbench via ODBC connection. I noticed that DOMO does not recognize 2 of the 3 date columns as dates. It thinks they are text fields. I have formatted them all the same in my sql statement and cannot figure out why it would recognize one but not the other.

 

CONVERT(char(10), c.CreatedDate, 126) AS [Create Date],                  -- comes through as text
CONVERT(char(10), c.Modified_Date__c, 126) AS [Status Last Modified],  -- comes through as text

CONVERT(char(10), c.Latest_Visit__c, 126) AS [Last Visit],                  -- comes through as date

 

(screen shots of dataset columns and dimensions as seen as date and text also attached)

 

They are all coming from SQL date formatted columns. The only difference in the source SQL data that I could see was that the one that DOMO recognizes has a time stamp of zero. The other two have times. However, I am stripping the time in the sql statement, so this shouldn't matter to DOMO, right?

 

I'd like to avoid having to make a calculated field for the date fields for every card I make on this dataset. Any ideas as to what is wrong? Thanks!!

Best Answer

  • RGranada
    RGranada 🟢
    Answer ✓

    Hi,

     

    Just an idea... did you check the field data type, on WorkBench Shema Separator? Some times the default data type attributed by workbench is not the one desired, and you can change it.

     

    If you change it, don't forget to check the "Allow Schema changes" checkbox in the job settings.

     

    Hope this helps.

    Ricardo Granada 

    [email protected]

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • neverest
    neverest

    Perfect- that did the trick. Thank you so much!

     

    For future readers of this post, make sure you check "Allow Schema Changes" before you run it. If you don't, you'll have to check "Upload even if data hasn't changed" as well as "Allow Schema Changes" and run it again. That finally made the change come through.

Categories