Date valid function

[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.

[chulett]

Why are you using "yyyymmdd" in the function when your date is in "mmddyyyy" format?

[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.

[ray.wurlod]

Make sure all dates have eight characters, so that they match the format string.

Code: Select all

Right("00":InLink.TheString, 8)

[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.

[RPhani]

Hi,

1)Please check Target column datatype.
2)'%mm%dd%yyyy' is Varchar(8) But defalt value in Else part is 10.

-------------------
RPhani

[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()

[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.