Convert String To Date

[dieselrocks]

To the Experts: I have a string field I need to convert to date format. I have the following formats:

mm/dd/yyyy
mm/d/yyyy
m/dd/yyyy
m/d/yyyy

What to do?? can I use Cdate?? if so, how to get around the different formats?

Thanks!!

[MMcCarthy]

CDate should handle any of the formats you've mentioned. Try it out on a test query and see how it looks.

SELECT DateField, CDate(DateField) FROM TableName
 

[dieselrocks]

I'm working in a querty window...Getting some strange results...Here is my formula: AcDate: CDate([action date]).

Most of the result fields contain this: #ERROR.

A field with this value 02/04/2009 Returns 10/29/7490 ??????

[MMcCarthy]

Can you attach a file with the data so I can look at it.

Mary

[dieselrocks]

Here it is. I exported it to a dbf. Some of the values are formated mm/dd/yyyy some are mmddyyyy.

[dieselrocks]

One more time....It's a zip file containing a DBF

[NeoPa]

@dieselrocks
What is a DBF file?

How can some be mmddyyyy now when they weren't in your first post?

I think this requires more of an explanation than dropping it into some post with some other stuff.

What exactly is the problem?

[MMcCarthy]

Just put them in a *.txt or *.csv file. It's a lot easier to deal with.

[missinglinq]

The mmddyyyy data is causing the problem. In order for CDate() to work correctly the data has to look like a date.

These all look like dates:

mm/dd/yyyy
mm/d/yyyy
m/dd/yyyy
m/d/yyyy

This doesn't:

mmddyyyy

You're going to have to make these last "dates" look like dates before using CDate().

A value like 122009 could be parsed correctly and slashes placed appropriately to make it look like a date, because it has 6 characters. Assuming that the last four represent the year, it could only represent 1/2/2009.

But a value like 1292009 cannot be parsed correctly, because it could be 1/29/2009 or 12/9/2009.

I think you're going to have to do this by manually changing the mmddyyyy fields, changing the obvious values (those that could only be interpreted in one way) and using other documentation to decide which is the correct interpretation for "dates" like like 1292009 which could be 1/29/2009 or 12/9/2009, then running your query using CDate().

What I'd do is create a query to pull all of these records, change them manually, then run your CDate query against the table.

To do this, create a select query against your table with the original "date" field, and a new field with this in the name box:

IsDate([OriginalDateFieldName])

Now, in this field's Criteria box enter "0", quotes and all.

When you run this query it will return all records whose "date" field cannot be evaluated as a date, and you can manually insert the slashes in the correct positions.

After doing this you can run your query using the CDate() function.

The most important thing, of course, is to insure that the dates are entered appropriately in the future.

Linq ;0)>

[dieselrocks]

Thanks Linq, your advice was right on!

[missinglinq]

Glad we could help!

Linq ;0)>