Hi,
I have column in a query that contains dates in text format and I need to convert them to short date so I can manipulate them. What is the easiest way to do this? Presently the date looks like this
20100121 - text
and I need it to look like 1/12/2010 short date format.
Thanks,
Leesha
Convert date in text format to short date format
-
- BronzeLounger
- Posts: 1492
- Joined: 05 Feb 2010, 22:25
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Convert date in text format to short date format
You can either calculate the date in a select query (so the date value wouldn't actually be stored, but calculated on the fly each time you open the query), or add a date/time field to the underlying table and populate it using an update query.
In either case, the expression to calculate the date/time value would look like this:
DateSerial(Left([TextField],4),Mid([TextField],5,2),Right([TextField],2))
where TextField is the name of the text column.
Note: I assume that you meant 01/21/2010 instead of 01/12/2010.
In either case, the expression to calculate the date/time value would look like this:
DateSerial(Left([TextField],4),Mid([TextField],5,2),Right([TextField],2))
where TextField is the name of the text column.
Note: I assume that you meant 01/21/2010 instead of 01/12/2010.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1492
- Joined: 05 Feb 2010, 22:25
Re: Convert date in text format to short date format
Wow! No wonder I would've never come up with that code!! And yes, I did mean 21 not 12. Too many hours starring at the computer screen.
Thanks!
Leesha
Thanks!
Leesha