How to cast as datetime in Domo workbench?

Reply
Highlighted
White Belt

How to cast as datetime in Domo workbench?

I need to add a date field to a job in workbench where the date is static, but not the current date.

 

I have been attempting to create a calculated transform and expected to do something similar to:

to_date('05/23/2018') or cast('2018-05-23' as datetime) 

Neither of those are available functions, however, and I don't see anything like them in the list of available date/time functions. 

 

Incidentally, I have played with changing quotes, etc, but I cannot get a calculation to validate.

 

There was a similar question asked and resolved by using the today() function, but I need to set to a previous date.

 

I can set the calculation to a simple sting (05/23/2018) easily enough, but then the schema either converts the field to a string, or nulls the cells as invalid (because that is the default setting) if I lock the data type.  

 

Not sure what I am missing.  Please help me create a date field set to static previous date.  Thanks in advance

 

 

Moderator

Re: How to cast as datetime in Domo workbench?

Anyone can help out with this request?

 

Thanks,

 

Purple Belt

Re: How to cast as datetime in Domo workbench?

Hi Laurette,

 

You can use the convert function in the workbench. I just tried, CONVERT('2017-01-01',DATE) AS date and it came through as a date format (1/1/2017) in Domo.

 

Let me know if this helps.

 

Thanks,

 

Brian

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Purple Belt

Re: How to cast as datetime in Domo workbench?

Hi Laurette,

 

Did the above help out? Additionally, you can do the same with DATETIME as I see that is referenced in your subject. 

 

 (CONVERT('YYYY-MM-DD HH:MM(put colon here, it kept making a smiley face when I used one in this post)SS', DATETIME)

ie: CONVERT('2018-01-01 07:22:45', DATETIME)

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
White Belt

Re: How to cast as datetime in Domo workbench?

I agree, those should both work, but just do not for me.  I know I had it working for a one-off ingestion job a few months ago, and I seem to remember using CONVERT then. I'll keep playing with it. 

DateDoesNotWork.PNG

Purple Belt

Re: How to cast as datetime in Domo workbench?

Laurette,

 

I do not have much experience with transforms but it looks like calculation builder only has specific available functions, none which will calculate a date. Are you able to write the convert code directly in the source instead of using the calculation builder?

 

Brian


**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
White Belt

Re: How to cast as datetime in Domo workbench?

That is a good idea.  I think I'm going to take the ingestion off workbench and do it through the R plugin instead. Thanks for the help!

Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!