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.