Page 1 of 1
Warnings due to date_from_string,julian_day_from_date fns
Posted: Tue Feb 22, 2011 1:35 pm
by partheev123
Hello All,
In my requirement I need to convert a input field(COL1) which is read as String(Varchar(8)) to JulianDay.
Example of Input field value : 20110222
I used the following derivation in the Transformer.
JulianDayFromDate(StringToDate(COL1,"%yyyy%mm%dd") )
I am getting the correct output but found two warnings in the DataStage log.
Warning: Conversion error calling conversion routine date_from_string data may have been lost
Warning: Conversion error calling conversion routine julian_day_from_date data may have been lost
Can you guys please suggest me ways to fix these warnings?
Posted: Wed Feb 23, 2011 8:29 am
by Ravi.K
Some where in your data there may be invalid date. Try for one record and check whether the warning appear or not.
Posted: Wed Feb 23, 2011 10:27 am
by partheev123
Hi Ravi..Thanks for the response...
I tried for one record but still have the same warnings.
Posted: Wed Feb 23, 2011 10:56 am
by greggknight
Try using this to make sure your data is correct.
and reject the row that has invalid date conversions
IsValidDate
Returns whether the given value is valid for the type date.
v Input: testdate (date)
v Output: result (int8)
v Examples. If the column mylink.mydate contains the date 2011-09-13, then the following
function returns the value 1.
IsValidDate(mylink.mydate)
If the column mylink.mydate contains the string "380096.06", then the following function
returns the value 0, because the converted string is not a valid date.
IsValidDate(StringTodate (mylink.mydate))
Posted: Fri Feb 25, 2011 12:41 am
by ravireddy25
greggknight wrote:Try using this to make sure your data is correct.
and reject the row that has invalid date conversions
IsValidDate
Returns whether the given value is valid for the type date.
v Input: testdate (date)
v Output: result (int8)
v Examples. If the column mylink.mydate contains the date 2011-09-13, then the following
function returns the value 1.
IsValidDate(mylink.mydate)
If the column mylink.mydate contains the string "380096.06", then the following function
returns the value 0, because the converted string is not a valid date.
IsValidDate(StringTodate (mylink.mydate))
Try First Convert the string to date format like(yyyy-mm-dd) then convert the date to Julian.
Regards
Ravi
Posted: Fri Feb 25, 2011 12:44 am
by ravireddy25
greggknight wrote:Try using this to make sure your data is correct.
and reject the row that has invalid date conversions
IsValidDate
Returns whether the given value is valid for the type date.
v Input: testdate (date)
v Output: result (int8)
v Examples. If the column mylink.mydate contains the date 2011-09-13, then the following
function returns the value 1.
IsValidDate(mylink.mydate)
If the column mylink.mydate contains the string "380096.06", then the following function
returns the value 0, because the converted string is not a valid date.
IsValidDate(StringTodate (mylink.mydate))
Try First Convert the string to date format like(yyyy-mm-dd) then convert the date to Julian.
Regards
Ravi
Posted: Fri Feb 25, 2011 12:44 am
by ravireddy25
greggknight wrote:Try using this to make sure your data is correct.
and reject the row that has invalid date conversions
IsValidDate
Returns whether the given value is valid for the type date.
v Input: testdate (date)
v Output: result (int8)
v Examples. If the column mylink.mydate contains the date 2011-09-13, then the following
function returns the value 1.
IsValidDate(mylink.mydate)
If the column mylink.mydate contains the string "380096.06", then the following function
returns the value 0, because the converted string is not a valid date.
IsValidDate(StringTodate (mylink.mydate))
Try First Convert the string to date format like(yyyy-mm-dd) then convert the date to Julian.
Regards
Ravi