Page 1 of 1
Date valid function
Posted: Thu Dec 19, 2013 2:23 pm
by hargun
hi ,
i am read the date column MATURITY_DATE(VARCHAR) with length 8 and converting into Date by using the function given below but getting NULL as output.
Code: Select all
if isnull(select_stats.MATURITY_DATE) then setnull() else if IsValid("Date" , select_stats.MATURITY_DATE, "%yyyy%mm%dd") Then StringToDate(select_stats.MATURITY_DATE,"%yyyy%mm%dd") else setnull()
From source i am getting the date like this
99142013
3302014
10312013
04052014
03312014
06072014
09032014
10032014
05272013
If the date is valid like 06072014 then i should get data in output but not getting.
Job is also working fine and not throwing any error.
Can somebody help me on this.
Posted: Thu Dec 19, 2013 2:29 pm
by chulett
Why are you using "yyyymmdd" in the function when your date is in "mmddyyyy" format?
Posted: Thu Dec 19, 2013 2:39 pm
by hargun
i used now
Code: Select all
if isnull(select_stats.MATURITY_DATE) then setnull() else if IsValid("Date" , select_stats.MATURITY_DATE, "%mm%dd%yyyy") Then StringToDate(select_stats.MATURITY_DATE,"%mm%dd%yyyy") else setnull()
Still getting the Null output.
Posted: Thu Dec 19, 2013 3:06 pm
by ray.wurlod
Make sure all dates have eight characters, so that they match the format string.
Posted: Thu Dec 19, 2013 5:08 pm
by hargun
i tried with that too still not getting output for vaild dates
i have also tried by using the function below
Code: Select all
if isnull(select_stats.MATURITY_DATE) or select_stats.MATURITY_DATE=0 then setnull() else if (IsValidDate(StringToDate(select_stats.MATURITY_DATE,"%mm%dd%yyyy"))=1) then StringToDate(select_stats.MATURITY_DATE,'%mm%dd%yyyy') else '0001-01-01'
This function populate only '0001-01-01' not the dates .
Can someone help me on the exact code which give me valid dates in ouput.
Posted: Fri Dec 20, 2013 1:08 am
by RPhani
Hi,
1)Please check Target column datatype.
2)'%mm%dd%yyyy' is Varchar(8) But defalt value in Else part is 10.
-------------------
RPhani
Posted: Sat Dec 21, 2013 8:13 pm
by hargun
target datatype is Date and i have also tried with function also but getting NULL as output not valid dates present in input.
Code: Select all
if isnull(select_stats.RENEWAL_DATE) then setnull() else if IsValid('Date' ,StringToDate(select_stats.RENEWAL_DATE, "%mm%dd%yyyy")) Then StringToDate(select_stats.RENEWAL_DATE,"%yyyy%mm%dd") else setnull()
Posted: Sat Dec 21, 2013 8:47 pm
by chulett
We seem to be missing some vital piece of information or this would have been solved some time ago seeing as how you've tried about every combination under the sun. What exactly is your source and how you are bringing it into the job? Are you certain that the format you posted as an example is accurate and the values are coming into the job properly?
If you haven't done so already, throw in a Peek stage before you attempt the conversion as a sanity check.