Converting Text to Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm importing a .csv file and some of the fields are dates in the format of
"YYYYMMDD". How do I convert them from text to dates in the format of
MMDDYYYY?
 
Whenever I have to do this, I usually create an temporary database that
contains temporary tables which contain all of the import fields, and any
extra fields I need to ensure that the fields are formatted properly. I link
these temporary tables to my database to limit the growth/shrinkage in my
actual database. I also setup import specifications to make the import
process go easier. Once I have imported the csv into my temp table, I
perform any of the translations that need to be performed.

In this particular case, you can use the Dateserial( ) function combined
with Left, Mid, and Right functions to parse the "date" and get it into the
appropriate format.

strDate = "20071230"
?dateserial(val(left(strDate, 4)), val(mid(strDate, 5, 2)),
val(right(strDate, 2)))

will display 12/20/2007 in the Immediate window.

HTH
Dale
 
Thanks - simple terms, easy to understand. :)

Dale Fye said:
Whenever I have to do this, I usually create an temporary database that
contains temporary tables which contain all of the import fields, and any
extra fields I need to ensure that the fields are formatted properly. I link
these temporary tables to my database to limit the growth/shrinkage in my
actual database. I also setup import specifications to make the import
process go easier. Once I have imported the csv into my temp table, I
perform any of the translations that need to be performed.

In this particular case, you can use the Dateserial( ) function combined
with Left, Mid, and Right functions to parse the "date" and get it into the
appropriate format.

strDate = "20071230"
?dateserial(val(left(strDate, 4)), val(mid(strDate, 5, 2)),
val(right(strDate, 2)))

will display 12/20/2007 in the Immediate window.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.
Click to expand...
 
Back
Top