Why are all of these dates (Due_Date, PO_Date, Print_Date, Ship_Date) returning the date - 1 day

Roman_K

When I run the following query in SQL Server, four date fields (Due_Date, PO_Date, Print_Date, Ship_Date) return the correct date, but when I run my query in the workbench, the resulting 4 dates are -1 day (Example: date when run in SQL Server = 2023-05-04-00.00.00.000000, the correct date, but when run in the workbench my result = 2023-05-03T19:00:00). How can I resolve?

If this is due to my "timestamp_format" code logic, how else can I achieve the same result?

SELECT
MAX(RTRIM(CONCAT(RTRIM(dl.ENBECD), RTRIM(dl.ENBESK)))) AS Item_Code,
MAX(hd.C6IGNB) AS PO_Number,
MAX(hd.C6JQNB) AS Ship_Via,
MAX(hd.C6LNTX) AS Port,
MIN(timestamp_format(substr(digits(hd.C6C3DT+19000000),2,8), ''YYYYMMDD'')) as Print_Date,
MAX(timestamp_format(substr(digits(hd.C6C4DT+19000000),2,8), ''YYYYMMDD'')) as Ship_Date,
MAX(hd.C6H1ST) AS PO_Status,
MAX(hd.C6LJTX) AS Confirmed,
MAX(hd.C6PUSS) AS Type,
MAX(dl.ENFYNB) AS Vendor_Number,
MAX(dl.ENBECD) AS Item_Number,
MAX(dl.ENBESK) AS SKU,
MAX(dl.ENPCNB) AS Sequence_Number,
MAX(dl.ENKIQT) AS Order_Quantity,
MAX(dl.ENSIPR) AS FOB_Cost,
MAX(timestamp_format(substr(digits(dl.ENCWDT+19000000),2,8), ''YYYYMMDD'')) as Due_Date,
MIN(timestamp_format(substr(digits(dl.ENINDT+19000000),2,8), ''YYYYMMDD'')) as PO_Date
FROM
cwmpdta.pohead hd
JOIN
cwmpdta.podetl dl ON C6COMP = dl.ENCOMP AND C6IGNB = dl.ENIGNB

GROUP BY
dl.ENBECD, dl.ENBESK, hd.C6IGNB, dl.ENCWDT
ORDER BY Item_Code, PO_Number, Due_Date

GrantSmith

This is because Domo interprets all dates as UTC when they get imported. If your SQL server is configured for a timezone other than UTC you can use a date transform in your workbench job configuration to specify which timezone you're importing with.

GrantSmith

This is because Domo interprets all dates as UTC when they get imported. If your SQL server is configured for a timezone other than UTC you can use a date transform in your workbench job configuration to specify which timezone you're importing with.

Roman_K

From my workbench query I am bringing in data that contains two fields that are naturally date/time (OrderDate and TransactionDate), and I just want to bring in those values as they are naturally (8/26/2024 9:00:49 AM…..bring in as exactly 8/26/2024 9:00:49 AM), without a "Shift Data Timezone Transform". Is there a setting in the workbench that I might be overlooking, I need the date/time stamp exactly as is?