Text to Date Conversion Using SQL Select Query

[JohnHarkins]

I have a date in text format that needs to be treated as if it were a data (i.e., #mm/dd/yy#). Maybe there's a better way to do it, but I have a number of dates in various months that need to be converted to the first day of the respective months so that I can consider all dates within a month to be equal.

Essentially, I want a function to convert the mm/dd/yy date to mm/01/yy and, because the output of the select query is going into a union query, I want to be sure that the result of the select query's date field (now in text format) to be converted "on assignment" to a date or datetime field. Have I overlooked a helpful Access function or SQL datatype assignment parameter?

Thanks!

John Harkins

 

[Blorf]

Try this:

Myfield: DateValue(Format(DateValue("01/15/2004&quot,"mm/""01""/yyyy&quot)

This will convert the String into a date, force the date into a string with 01 for the day, then take that string and make into a date.

Replace the "01/15/2004" with your Field name in your query, and you will be on your way.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[JohnHarkins]

How simple; how elegant!

John Harkins

 

[Blorf]

Glad to be helpful.

And Simple.

And Elegent.

LOL

ChaZ

Ascii dumb question, get a dumb Ansi