Null handling for dates/timestamps in MOdify Stage
Moderators: chulett, rschirm, roy
Null handling for dates/timestamps in MOdify Stage
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
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
-
- Participant
- Posts: 15
- Joined: Mon Mar 20, 2006 2:03 pm
Re: Null handling for dates/timestamps in MOdify Stage
1. you can do it in Transformer with IsNull functionjreddy wrote: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
2. Use DS macro DSJobStartDate
What's the next?
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Try this
You will get output defaulted to "0001-01-01" if the input date is coming as null.
Code: Select all
OutPutDateColumnName = handle_null(InputdateColumnName)
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
I doubt if we can Handle_Null work for dates with CurrentDate or any user specified date.
But below code worked (Without specifying any default value). Output defaulted to "0001-01-01"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.
OutPutDateColumnName = handle_null(InputdateColumnName)
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
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
[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 !!
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 !!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.