Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to convert incoming data from DB2 as source into Kafka as Target.
The dates in DB2 are coming in as Numeric (8,0) in the Task when I select the tables
They are defined as numeric with values as YYYYMMDD or 99999999
I want to define the function mapping using one of the date time functions to translate the inputs coming in as 20220525 or 99999999 into valid date format. I couldn't find an example of what the modifier should be if using these functions
Thank you
Hi adbdkb,
I think that you will need to use this type of function to format the date.
strftime('%Y %m %d','now');
This example used the 'now' which is a date. I am not sure if you could just replace 'now' with your numeric field or if you will need to massage the numeric field into a string.
Alternatively you could cast the number as a string and then do some substr() functions to pull the year month and day out into the correct format.
As to handling the 99999999 you may need to wrap this in a case statement to catch and deal with it seperatly.
I hope this starts to help you out.
Thanks,
Michael
Just declare the column as DATE (whichever flavor) using a transformation with SUBSTR functions on the NUMERIC to take it apart and glue back together with dashes.
SQLite will do the number to text for you, as well as the text to date.
The below works for me (those \n are non-functional whitespace newlines to enhance readability in the UI:
The tricky part is to decide what to do with invalid dates. For the you could first feed the thusly assembled date string into another date-time function and look for a result. If there is a valid result provide the assembled (again) date else a trigger value like 1900-01-01. That's left as an exercise to the reader based on actual needs. I would NOT bother with 1900 < YYYYY < 2300, 0 < MM < 13, 0 < DD < 32.
Hein.
"table_manipulation": {
"owner": "dbo",
"name": "test",
"transform_columns": [{
"column_name": "yyyymmdd",
"action": "KEEP",
"new_data_type": "kAR_DATA_TYPE_DATE",
"computation_expression": "substr($yyyymmdd,1,4) || '-' ||\nsubstr($yyyymmdd,5,2) || '-' ||\nsubstr($yyyymmdd,7,2)\n"
}, ....
Hi adbdkb,
I think that you will need to use this type of function to format the date.
strftime('%Y %m %d','now');
This example used the 'now' which is a date. I am not sure if you could just replace 'now' with your numeric field or if you will need to massage the numeric field into a string.
Alternatively you could cast the number as a string and then do some substr() functions to pull the year month and day out into the correct format.
As to handling the 99999999 you may need to wrap this in a case statement to catch and deal with it seperatly.
I hope this starts to help you out.
Thanks,
Michael
Thanks. So, when using the date(...) function, the first parameter is the string. I am trying to understand why it shows many modifiers and what is expected for the modifiers ?
date(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
I am a bit confused about what is needed for the modifiers. Or how the modifiers are to be used
Also, where can I find an example of case statement that you have mentioned.
Thanks
Here is a substring that will rearrange the format:
substr($NumericField,5,2) || '/' || substr($NumericField,7,2) || '/' || substr($NumericField,0,5)
Here is a quick example of a case statement format:
CASE
WHEN $AR_H_STREAM_POSITION != ''
THEN
strftime('%Y-%m-%d',$HCD_FROM_YMD) = source_lookup('NO_CACHING', 'DBO', 'MyMaxTest', 'max(HCD_FROM_YMD)','1=?', '1')
ELSE 1
END
As for the documentation on the date time functions the best bet is to google SQLite syntax and check out there links.
Thanks,
Michael
Thanks. I am not sure what am I missing. When I do 'Test Expression', nothing happens. I will take a look at SQLite for syntax. Just want to make sure the way I am calling it here is correct in terms of using it inside Replicate
Thanks. Will try that. Can the CASE statement still be added to check if the value is all 9, then set the value to say 20991231 ?
Hi adbdkb,
Yes, that is what I would do.
Thanks,
Michael
Just declare the column as DATE (whichever flavor) using a transformation with SUBSTR functions on the NUMERIC to take it apart and glue back together with dashes.
SQLite will do the number to text for you, as well as the text to date.
The below works for me (those \n are non-functional whitespace newlines to enhance readability in the UI:
The tricky part is to decide what to do with invalid dates. For the you could first feed the thusly assembled date string into another date-time function and look for a result. If there is a valid result provide the assembled (again) date else a trigger value like 1900-01-01. That's left as an exercise to the reader based on actual needs. I would NOT bother with 1900 < YYYYY < 2300, 0 < MM < 13, 0 < DD < 32.
Hein.
"table_manipulation": {
"owner": "dbo",
"name": "test",
"transform_columns": [{
"column_name": "yyyymmdd",
"action": "KEEP",
"new_data_type": "kAR_DATA_TYPE_DATE",
"computation_expression": "substr($yyyymmdd,1,4) || '-' ||\nsubstr($yyyymmdd,5,2) || '-' ||\nsubstr($yyyymmdd,7,2)\n"
}, ....