Import date/time into Access

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

Guest

I am trying to import date/time information from a .csv file. The information
is in iso format of YYYY-MM-DDTHH:MM:SSZ. How can I get this into a datetime
field of Access. Also, does Access actually store a date and time in a single
field, or must they be broken into a date field and a time field?

Thanks,
Dave
 
May be the long way but it works:

in a query do something like this:

Year:Left([datefield],4), Month:Mid([datefield],6,2),
Day:Mid([datefield],9,2), Time:Mid([datefield],etc,etc)

then NewDate:[Month]&"/"&[Day]&"/"&[Year]&" "&[Time]

and finally FormattedDate:CVDate([NewDate])

see also entries at http://www.allenbrowne.com/tips.html
 
Hi Jeff,

Thanks for your quick reply. This works for me. It seems my problem was with
the letters T and Z used as delimeters and the Access time\date field holds
both date and time together just fine.

-Dave

Jeff C said:
May be the long way but it works:

in a query do something like this:

Year:Left([datefield],4), Month:Mid([datefield],6,2),
Day:Mid([datefield],9,2), Time:Mid([datefield],etc,etc)

then NewDate:[Month]&"/"&[Day]&"/"&[Year]&" "&[Time]

and finally FormattedDate:CVDate([NewDate])

see also entries at http://www.allenbrowne.com/tips.html
--
Jeff C
Live Well .. Be Happy In All You Do


DaveLerman said:
I am trying to import date/time information from a .csv file. The information
is in iso format of YYYY-MM-DDTHH:MM:SSZ. How can I get this into a datetime
field of Access. Also, does Access actually store a date and time in a single
field, or must they be broken into a date field and a time field?

Thanks,
Dave
 
I'd recommend using

NewDate: DateSerial([Year], [Month], [Day]) + CDate([Time])

Not everyone uses mm/dd/yyyy as their Short Date format!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jeff C said:
May be the long way but it works:

in a query do something like this:

Year:Left([datefield],4), Month:Mid([datefield],6,2),
Day:Mid([datefield],9,2), Time:Mid([datefield],etc,etc)

then NewDate:[Month]&"/"&[Day]&"/"&[Year]&" "&[Time]

and finally FormattedDate:CVDate([NewDate])

see also entries at http://www.allenbrowne.com/tips.html
--
Jeff C
Live Well .. Be Happy In All You Do


DaveLerman said:
I am trying to import date/time information from a .csv file. The
information
is in iso format of YYYY-MM-DDTHH:MM:SSZ. How can I get this into a
datetime
field of Access. Also, does Access actually store a date and time in a
single
field, or must they be broken into a date field and a time field?

Thanks,
Dave
 
Actually I suspect that more people don't use mm/dd/yyyy than do.


Douglas J. Steele said:
I'd recommend using

NewDate: DateSerial([Year], [Month], [Day]) + CDate([Time])

Not everyone uses mm/dd/yyyy as their Short Date format!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jeff C said:
May be the long way but it works:

in a query do something like this:

Year:Left([datefield],4), Month:Mid([datefield],6,2),
Day:Mid([datefield],9,2), Time:Mid([datefield],etc,etc)

then NewDate:[Month]&"/"&[Day]&"/"&[Year]&" "&[Time]

and finally FormattedDate:CVDate([NewDate])

see also entries at http://www.allenbrowne.com/tips.html
--
Jeff C
Live Well .. Be Happy In All You Do


DaveLerman said:
I am trying to import date/time information from a .csv file. The
information
is in iso format of YYYY-MM-DDTHH:MM:SSZ. How can I get this into a
datetime
field of Access. Also, does Access actually store a date and time in a
single
field, or must they be broken into a date field and a time field?

Thanks,
Dave
 
I just ran into this problem myself. Possibly because I use import
specifications this problem appears to be a trivial matter. Perhaps an MVP
can tell me what I am overlooking. (Hint: I found this solution out by
error.)

The database was created and the field made Date/Time.

The import specification was created and the field made Text.

The string is properly converted to a date/time field. This was tested on
Access 2002 (10.43) SP3.

JimO
 
How can I get this into a datetime field of Access.

I use Import Specifications for importing text files. The solution I
accidentally found works but perhaps an MVP will tell my why it shouldn't be
used.

The Import Specification field is defined as text.
The Database field is defined as Date/Time.

All is well.


PS. In case of a double post please accept my apologies. I waited about
almost an hour (I think) and my response still had not appeared.
 
Back
Top