StringToDate unusual scenario

pandeesh · Post by **pandeesh** » Mon Jan 09, 2012 1:11 am

Hi,

One of my input file contains the date in the form of YYMMDD format.
But the value "111011" can be either 11-10-2011 or 11-10-1911.
It's still unambiguous whether i need to choose, 1911 or 2011.
Is there any inbuilt function to take automatically as the current century?
Currently, i am passing a parameter "cutoff_year . if it's 2000, that will be converted to 2011 else if it's 1900 then that will be converted to 1911.

Is there any other way to achieve this?

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jan 09, 2012 4:30 am

Search for CENTURYPIVOT.

pandeesh · Post by **pandeesh** » Mon Jan 09, 2012 6:33 am

I have searched and found that simple if then else check for century will work.
But in my case i am receiving the past years data in file.
So, it will make some issues in the year of 2100.
For the current date century will be 21 and if any date comes like 100112 then that will be turned to "2110-01-12".
It will be better , if they give the data in yyyymmdd format in stead of yymmdd.

Thanks

srds2 · Post by **srds2** » Mon Jan 09, 2012 9:57 am

I guess you can concatenate Left(CurrentDate(),'2') to YY before you convert it to YYYY

pandeesh · Post by **pandeesh** » Mon Jan 09, 2012 10:02 am

Yes. But I an receiving the old data .
May be last year . I have explained clearly in my previous post when it will create a problem

ray.wurlod · Post by **ray.wurlod** » Mon Jan 09, 2012 3:34 pm

How do you know that you're receiving previous century's data? Leverage that knowledge to process your dates correctly.

pandeesh · Post by **pandeesh** » Mon Jan 09, 2012 11:13 pm

Thanks all! I will check..

PhilHibbs · Post by **PhilHibbs** » Tue Jan 10, 2012 11:29 am

I don't think it's a good idea to use a single system setting to drive all YY to YYYY conversions. You might be dealing with birth dates in one column and pension maturity dates in another, the former would always be in the past and the latter would more likely be in the future or the recent past, so a single system-wide pivot date is not the right solution. Each column should have its own conversion logic that is appropriate for the kind of date that is being converted, and some might even depend on other data within the record e.g. the midpoint date (the date that the result must be within +/- 50 years of) for a person's pension maturity date might be their date of birth + 60 years.