Converting Partial Date (text) to searchable Date?

[robear]

What Calculation, Let statement or Custom function can convert a text field "Dec/2011" into a searchable Date range.

In this case every record in the table has a field called Period (set by a script at time of importing data).
The Period field is Text and always contains Mth/Year, i.e. Dec/2011, Jan/2012....

Objective: to search for Periods within a Range as either:
Jun/2011...Nov/2011 or
6/1/2011...11/30/2011 or
2011/06/01...2011/11/30


robear

[AHunter3]

Method A: Calculation field result type "date" defined as

Let ([
MonthName = Left (YourTextField; 3);
MonthStack = "Jan¶Feb&Mar¶Apr¶May¶Jun¶Jul¶Aug¶Sep¶Oct¶Nov¶D ec";
Cut = Middle (MonthStack; 1; Position (MonthStack; MonthName; 1; 1);
mon = PatternCount (Cut; "¶")+1;
yr = Right (YourTextField; 4)
];

Date (mon; 1; yr)
)


Search this calculation field using regular date ranges.

6/1/2011...11/30/2011



Method B: Calculation field result type "number" defined as

Let ([
MonthName = Left (YourTextField; 3);
MonthStack = "Jan¶Feb&Mar¶Apr¶May¶Jun¶Jul¶Aug¶Sep¶Oct¶Nov¶D ec";
Cut = Middle (MonthStack; 1; Position (MonthStack; MonthName; 1; 1);
mon = PatternCount (Cut; "¶")+1;
yr = Right (YourTextField; 4)
];

yr & Right ("0" & mon; 2)

Search this calculation field using this type of nomenclature:

201106...201111

[Techphan]

Jun/2011...Nov/2011

Use a calculated field, text result **OR** a looping script that uses this calculation to populate a text field:

Date (
Case (
Left (Text Date;3) = "Jan" ; "1" ;
Left (Text Date;3) = "Feb" ; "2" ;
Left (Text Date;3) = "Mar" ; "3" ;
Left (Text Date;3) = "Apr" ; "4" ;
Left (Text Date;3) = "May" ; "5" ;
Left (Text Date;3) = "Jun" ; "6" ;
Left (Text Date;3) = "Jul" ; "7" ;
Left (Text Date;3) = "Aug" ; "8" ;
Left (Text Date;3) = "Sep" ; "9" ;
Left (Text Date;3) = "Oct" ; "10" ;
Left (Text Date;3) = "Nov" ; "11" ;
Left (Text Date;3) = "Dec" ; "12" ) ; "1" ; Middle (Text Date;5;4))

& ".." &

Date (
Case (
Middle (Text Date;12;3) = "Jan" ; "1" ;
Middle (Text Date;12;3) = "Feb" ; "2" ;
Middle (Text Date;12;3) = "Mar" ; "3" ;
Middle (Text Date;12;3) = "Apr" ; "4" ;
Middle (Text Date;12;3) = "May" ; "5" ;
Middle (Text Date;12;3) = "Jun" ; "6" ;
Middle (Text Date;12;3) = "Jul" ; "7" ;
Middle (Text Date;12;3) = "Aug" ; "8" ;
Middle (Text Date;12;3) = "Sep" ; "9" ;
Middle (Text Date;12;3) = "Oct" ; "10" ;
Middle (Text Date;12;3) = "Nov" ; "11" ;
Middle (Text Date;12;3) = "Dec" ; "12" ) ; "1" ; Right (Text Date;4))


Gives this result which you can use to search a date range ---> 6/1/2011..11/1/2011


Since you did not specify days and those are needed (bold red above) you can tweak the equation. To get the last day of ANY month use this calculation: Date ((Month + 1; 1; Year)-1) Edit: ---> this is easier to get last day of any month ---> Date ( Month + 1 ; 0 ; Year)

[robear]

Hi

Not sure if I missed a piece but Method B (the desired nomenclature) throws an error:
"too many parameters" and highlites the colon at end of Cut line.

robear

[chopper]

What Calculation, Let statement or Custom function can convert a text field "Dec/2011" into a searchable Date range.

As long as "Dec/2011" is the only thing in the field

Use this Custom Function http://www.briandunning.com/cf/805

MonthNumber

Change the parameter to Month_Name, in the parameter area and as well in the calculation area.
since FM won't allow you to use MonthName because it is a FM command word.

Should be MonthNumber(Month_Name)


In your Calculation to draw the date out simply do this

GetAsDate(Date(MonthNumber ( YourTable::YourFieldYourTable ); 1 ; Right(YourTable::YourFieldYourTable;4) ) )

[HBMarlowe]

What Calculation, Let statement or Custom function can convert a text field "Dec/2011" into a searchable Date range.

In this case every record in the table has a field called Period (set by a script at time of importing data).
The Period field is Text and always contains Mth/Year, i.e. Dec/2011, Jan/2012....

Objective: to search for Periods within a Range as either:
Jun/2011...Nov/2011 or
6/1/2011...11/30/2011 or
2011/06/01...2011/11/30


robear

In order to be able to search in all of the ways that you list the calculation is going to have to return text, rather than date. Furthermore there will have to be many values in the field, separated by returns. In my opinion it would be better to use one of the solutions shown above to return a date e.g., 6/1/2011. Then script the search process so you can parse the search term to a variable. Regardless of the input format return only m/yyyy to the variable, or in the case of a range m/yyyy...m/yyyy. In this way you can find any day within the month even though all of the dates are actually on the first day.

[robear]

Method A: Calculation field result type "date" defined as

Search this calculation field using this type of nomenclature:

201106...201111

Fixed the missing ) in the Let statement.

Result: partially worked
Dec/2011 -> 201111
Jan/2012 -> 201201
Feb/2012 -> 201202
Mar/2012 -> 201202
Apr/2012 -> 201203

Looks like Feb being a short month affected the calc!

robear

[robear]

Use a calculated field, text result **OR** a looping script that uses this calculation to populate a text field:...
To get the last day of ANY month use this calculation: Date ((Month + 1; 1; Year)-1) Edit: ---> this is easier to get last day of any month ---> Date ( Month + 1 ; 0 ; Year)

Thank you Techplan

Given the conditions (CAD dates...), a looping script "during import" was the easiest solution, and your other tips for Last day of Month & Year are appreciated.

robear