Jump to content

Text dates to a date field


This topic is 6543 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. crazy.gif 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? confused.gif

Pete

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6543 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.