Dataservices is sending wrong DATETIME value

[cader_berkay]

Hi All,

Hope you folks doing well,

I have a problem when I load data to Snowflake with Dataservice. I have two columns. I concate these columns then I am creating new columns with DateTime format. When I load the data value is wrong.

VBRK_ERDAT = '2019-08-07'

VBRK_ERZET = '08:21:40'

I am using the below converting.

to_date(to_char(STG_INVOICE_TABLE.VBRK_FKDAT||' '||STG_INVOICE_TABLE.VBRK_ERZET, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd hh24:mi:ss')

I load data then I check data in Snowflake. It is coming wrong.

Snowflake >> '2019-08-01 22:21:40.000'

Regards,

Mehmet

[jmuiruri]

Dear Cafer,

In the example provided above, you have provided columns VBRK_ERZET and VBRK_ERDAT but in the conversion script you are calling different date column VBRK_FKDAT i would like to think this is why you are getting 2019-08-01 instead of 2019-08-07.

In the conversion script i don't see why you need to use to_char, from the documentation to_char function is used to convert date to a string but the output of the concatenation between the two columns is a string already, which renders the to_char redundant in this case.

Here is an example that converts columns VBRK_FKDAT = '2019-08-07'; and VBRK_ERZET = '08:21:40'; to

2019.08.07 08:21:40

to_date((STG_INVOICE_TABLE.VBRK_FKDAT||' '||$STG_INVOICE_TABLE.VBRK_ERZET), 'YYYY-MM-DD HH24:MI:SS');

Please see the following SAP KBA on how to convert AM/PM formats to 24hr/12hr time formats

1831775 - How to convert string date and time values with AM/PM to 24hr datetime format? - Data Services

Best Regards,

Joseph

[cader_berkay]

joseph_muiruri

Thanks Joseph,

I used to to_date() function. I uploaded data again. Covert Date is coming different offsets. It isn't possible to use utc_to_local(). I added photo for date convert process. I tried to many way for finding true value.

to_date((STG_INVOICE_TABLE.VBRK_FKDAT||' '||STG_INVOICE_TABLE.VBRK_ERZET), 'YYYY.MM.DD HH24:MI:SS')

Best Regards,

[jmuiruri]

Dear Cafer,

I you wanted to use utc_to_local, then there is no need of using to_date function.

you could use for Example:

$VBRK_FKDAT = '2019-12-04';
$VBRK_ERZET = '10:58:32';
$results = utc_to_local( ($VBRK_FKDAT||' '||$VBRK_ERZET),'UTC+3');
print($results);

Results in:

2019.12.04 13:58:32 # offset 3hrs UTC+3 

Best Regards,

Joseph

[cader_berkay]

joseph_muiruri

Dear Joseph,

I used utc_to_local. Error still keep going.

utc_to_local((STG_INVOICE_TABLE.VBRK_FKDAT ||' '|| STG_INVOICE_TABLE.VBRK_ERZET), 'UTC+10')

I checked snowflake current timestamp.

select current_timestamp >> '2022-11-15 23:46:47.318 -0800'

My current hour is now >> '2022-11-16 10:46:47.318'

The photo below will help to understand the problem more closely. STAGE_DATE Merged version of VBRK_FKDAT and VBRK_ERZET. I want to see this date. I combine VBRK_FKDAT and VBRK_ERZET from Data Services and after I get 10 hours I send snowflake. This stands for INVOICEDATE. When I take the difference of INVOICEDATE and STAGE_DATE I get HOURDIFF. Randomly there are 10 and 11 hours difference. I'm trying to understand the problem.

Thank you for your interest.