convert TEXT field to DATE 1

[JonBartels]

I have a table where a date has been entered as a TEXT field, i.e. "12Aug00" (no quotes). I need to sort the records by Date order (an alpha sort would put 03Jul00 before 04Mar00).
Is there a function to convert this field to a valid date field? I can't figure out the syntax to work with DTSToDate, and I do not know if that is the function I am looking for.
THANK YOU ALL.

 

[LindaC]

Hi JonBartels,
I don't know of a function in CR to change a string in that format to a date. I would build the date in a formula and then sort the formula. I would create 3 formulas 1(Month), 2(Mon_to_Num) and 3(mmddyy) to transfer the month to a number using the String function Mid:

Month
Mid(yourtable.textdate,3,3)

This will extract the 3 char month from your string.

Mon_to_Num
if @month = 'JAN' then
'01'
else
if @month = 'FEB' then
'02'
.
.
ect.

Then concatenate the new string:

mmddyy
@Mon_to_num + Left(yourtable.textdate,2) +
Right(yourtable.textdate,2)

Then sort using @mmddyy.





LindaC
lcastner@co.orange.ny.us

 

[JonBartels]

thank you Linda!
I'll give it a try.