Text dates to a date field

[PiedPiper]

Importing ASCII text date fields, ranging from 1992 through 2004. Dates in the existing text fields are mm/dd/yy. 1-digit month and days always display with a leading zero.

I need to import into date fields. I need functioning dates as dates, not text. I created a date field and used a script with Set Field [ DateField, TextToDate(TextDateField)]. But I get 1902 for the year 2002. I don't know how to fix this now in the date field records. And these text files need to be imported regularly so the date conversion will need to happen then. Can I import direcly into the date fields but get the dates right when it does?

Pete

[Lee Smith]

I can't seem to duplicate your problem with the simple test file I made up. Can you attached a sample of your file.

HTH

Lee

[spb]

As it looks like your data will TextToDate correctly but for the year, you need to get the first two digits of the year calculated into your text before you convert to date. You could use an intermediary calculation field, or possibly place a Case statement into your TextToDate space:

Case(Right(TextDateField, 2) <10, Left(TextDateField, 6) & "20" & Right(TextDateField, 2),

Left(TextDateField, 6) & "19" & Right(TextDateField, 2))

This will build the year up to four digits, arbitrarily assuming any year less than 10 is a 2000 year and ten or greater is a 1900 year, though this is adjustable to fit your data (if you have data that goes way into the future, you'll have to decide if a year of 44 is 2044 or 1944).

Now you can wrap a TextToDate around this whole Case statement:

TextToDate(Case(Right(TextDateField, 2) <10, Left(TextDateField, 6) & "20" & Right(TextDateField, 2),

Left(TextDateField, 6) & "19" & Right(TextDateField, 2)))

Then place into your original Set Field:

SetField (DateField, TextToDate(Case(Right(TextDateField, 2) <10, Left(TextDateField, 6) & "20" & Right(TextDateField, 2),

Left(TextDateField, 6) & "19" & Right(TextDateField, 2))))

Unless I've made a mistake, that should do it.

Steve Brown