converting a date

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

Guest

hey all,

i have a decimal field that represents a date (20050404) how do I convert
that date to usa format so i can use those nice built-in functions to get the
weekday name?

thanks,
rodchar
 
If the data comes to you from an external source, you can convert it on
import by using an import spec. In the advanced section of the wizard, you
can specify field order and delimiter for date fields. If you want to
convert it after the fact, you should add a new date type field to your
table and then run an update query. Once you are certain that the data has
been converted properly, delete the old text column. Do I need to remind
you to back up before trying this?

YourNewDate = CDate(Mid(OldDate,5,2) & "/" & Right(OldDate, 2) & "/" &
Left(OldDate,4))
The above statement uses the Mid(), Right(), and Left() functions to extract
the appropriate parts of the original string and concatenate them with "/"
in the more standard month/day/year order.
 
thanks, that helped.

Pat Hartman said:
If the data comes to you from an external source, you can convert it on
import by using an import spec. In the advanced section of the wizard, you
can specify field order and delimiter for date fields. If you want to
convert it after the fact, you should add a new date type field to your
table and then run an update query. Once you are certain that the data has
been converted properly, delete the old text column. Do I need to remind
you to back up before trying this?

YourNewDate = CDate(Mid(OldDate,5,2) & "/" & Right(OldDate, 2) & "/" &
Left(OldDate,4))
The above statement uses the Mid(), Right(), and Left() functions to extract
the appropriate parts of the original string and concatenate them with "/"
in the more standard month/day/year order.
Click to expand...
 
i have a decimal field that represents a date (20050404) how do I
convert that date to usa format so i can use those nice built-in
functions to get the weekday name?
Click to expand...

The built-in date functions are entirely free of any locality
information: the Date data type is merely a serial number. It's only for
text and display purposes that USA or any other format becomes relevant.

In order to have a safe method of conversion, use the DateSerial
function:

MyNewDate = DateSerial(CInt(Mid(MyOldDate,1,4)), _
CInt(Mid(MyOldDate(5,2)), _
CInt(Mid(MyOldDate(7,2)))

.... which will still work even after the new Swiss temp has changed her
control panel settings: relying on a default text conversion is _not_
guaranteed.

All the best


Tim F
 
Back
Top