in the Domo workbench, can't separate TransactionDate (date/time format) into 2 pieces

Roman_K · 2024-07-27T17:43:38+00:00

There was an error rendering this rich post.

I have created a job in the Domo workbench with the following SQL query, but in my resulting dataset, "Transaction_Date" and "Transaction_Time" are not splitting into two columns, one with just the date (2021-09-02) and the other just the time (01:00:21.000)….I am still getting both (2021-09-02 01:00:21.000). I have also tried CONVERT with no success. Thanks in advance.

CONVERT(DATE, a.[TransactionDate]) AS Transaction_Date,    CONVERT(TIME, a.[TransactionDate]) AS Transaction_Time

SELECT
a.[OrderNumber],
a.[ShipToNumber],
a.[LineNumber],
a.[TransactionType],
a.[TransactionNumber],
a.[TransactionDate],
CAST(a.[TransactionDate] AS DATE) AS Transaction_Date,
CAST(a.[TransactionDate] AS TIME) AS Transaction_Time,
a.[SequenceNumber],
a.[VendorNumber]
FROM [Marketing].[dbo].[Sales] a with (nolock)

Find more posts tagged with

Accepted answers

All comments

GrantSmith

dates and times are still represented as timestamps. If you really just want the text values you’ll need to convert it to a string but will lose the ability to do date operations

Roman_K

Is there any other way to accomplish separating TransactionDate (date/time format) into 2 pieces in the workbench other than converting it to a string, I do need to be able to do date operations?