Null handling for dates/timestamps in MOdify Stage

[jreddy]

My source column is a NULLABLE value, but it needs to get dumped into target column that is NOT NULLABLE, so i have the modify stage to convert the nullability and am using the HANDLE_NULL function in specification..

but how do i do this
1) Set the default date to 1/1/1901 if source value is null
2) Set the default date to current timestamp if source value is null

Any advise is appreciated. Thanks

[wenfei.chen]

My source column is a NULLABLE value, but it needs to get dumped into target column that is NOT NULLABLE, so i have the modify stage to convert the nullability and am using the HANDLE_NULL function in specification..

but how do i do this
1) Set the default date to 1/1/1901 if source value is null
2) Set the default date to current timestamp if source value is null

Any advise is appreciated. Thanks

1. you can do it in Transformer with IsNull function
2. Use DS macro DSJobStartDate

[jreddy]

Can i do it in MODIFY stage? Am doing the null handling and datatype conversions on all other source columns in this stage, dont want to add a transformer extra just for the dates..

[us1aslam1us]

Yes, you can. Isn't Last time Ray posted you the syntax for that.

[jreddy]

Nope, i dont think so. I do have the tips on the general suggestion on how to handle nullability and datatype converstion with just one specification.. not leads to what functions i might be able to use to achieve date conversions or setting default dates !

[ray.wurlod]

handle_null() is apposite for any data type. The replacement value must be a constant, and compatible with the data type.

[jreddy]

thanks Ray,

I tried this syntax

date1 = handle_null(date2, '1/1/1900')

i also tried 1-1-1900 and other date formats, but couldnt get it to work, so i was trying to find the function that would default a date

[us1aslam1us]

Your syntax seems to be correct. What is your source and target datatypes and formats for this field?

[JoshGeorge]

Try this

Code: Select all

  OutPutDateColumnName = handle_null(InputdateColumnName) 

You will get output defaulted to "0001-01-01" if the input date is coming as null.

[ray.wurlod]

What is your default date format string (set in Administrator, on Parallel tab)? The date format you specify in handle_null must match this string.

[JoshGeorge]

I doubt if we can Handle_Null work for dates with CurrentDate or any user specified date.

Even if we specify the default date format string as set in Administrator, on Parallel tab (YYYY-MM-DD in my project settings).

OutPutDateColumnName = handle_null(InputdateColumnName, '1999-01-01')

or

OutPutDateColumnName:date = handle_null(InputdateColumnName, '1999-01-01')

Never worked for me.

But below code worked (Without specifying any default value). Output defaulted to "0001-01-01"

OutPutDateColumnName = handle_null(InputdateColumnName)

[jreddy]

The column has been defined as Timestamp in both before and after tabs of Modify stage.. the source to the Modify stage is a Oracle Enterprise stage reading from an oracle table - which has this column defined as Date in Oracle DB - but when i imported the metadata into DataStage, i guess it got stamped as a Timestamp

[jreddy]

[quote="ray.wurlod"]What is your default date format string (set in Administrator, on Parallel tab)? The date format you specify in handle_null must match this string. ...[/quote]

thanks much Ray,
this worked.. the default timestamp was of format yyyy-mm-dd hh:mi:ss in administrator,
so i used this

date2 = handle_null(date1, '1900-01-01 00:00:00)

and it worked !!

[ray.wurlod]

Curious. They usually use nn for minutes (rather than mi), and you really would need matching quotes around your format string. But if you say it works, who am I to disbelieve you?