|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
--
Jeff C
Live Well .. Be Happy In All You Do
"DaveLerman" wrote:
[Quoted Text] > 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
|
|
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" wrote:
[Quoted Text] > 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" wrote: > > > 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
http://I.Am/DougSteele
(no private e-mails, please)
"Jeff C" <JeffC[ at ]discussions.microsoft.com> wrote in message
news:FD334183-E90C-4E8D-A232-556DB1AC2F2E[ at ]microsoft.com...
[Quoted Text] > 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" wrote: > >> 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" wrote:
[Quoted Text] > 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 > http://I.Am/DougSteele> (no private e-mails, please) > > > "Jeff C" <JeffC[ at ]discussions.microsoft.com> wrote in message > news:FD334183-E90C-4E8D-A232-556DB1AC2F2E[ at ]microsoft.com... > > 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" wrote: > > > >> 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
|
|
|
[Quoted Text] >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.
|
|
|