I spent a week or so dealing with date conversion issues. I am moving data
between Access, Oracle and SQL Server and have had to master their
vicissitudes. Here is what I discovered about formatting dates for ODBC
(Access and SQL Server). The official version (from the DBI or DBD
documentation somewhere) differs from my experience.
# How to insert dates/times in ODBC (Official Version)
#
# ODBC has a special way of handling dates which is generic for all DBMS's.
# You should use the following formats in your statements:
# Dates: {d 'YYYY-MM-DD'}
# Times: {t 'HH:MM:SS'}
# TimeStamp or DateTime: {ts 'YYYY-MM-DD HH:MM:SS'}
#
# The best way to handle this is to use sprintf:
#
# $ODBCDate = sprintf("{d '%4d-%02d-%02d'}", $year, $mon, $day);
# Note 1: Years must be 4 digits.
# Note 2: When you do a SELECT the dates get returned in whatever format
# is set for the database, not the ODBC format.
#
# When the above fails to work (as it sometimes does) try this format:
#
# Dates: 'YYYY-MM-DD'
# Times: 'HH:MM:SS' (optional AM PM: 'HH:MM:SS PM")
# Timestamp: 'YYYY-MM-DD HH:MM:SS'
#
# This format seems to work for Access and SQL Server 7.
Hope this helps. I use parameter binding, so I do not have to worry about #
vs ' vs " bracketing issues.
- Paul
At 09:45 AM 4/27/2001 -0600, Sterin, Ilya wrote:
>-----Original Message-----
>From: Bodo Eing
>To: Maria Quinn
>Cc: 'mi email '; '[EMAIL PROTECTED] '
>Sent: 04/27/2001 9:03 AM
>Subject: RE: How to add Date records to MS ACCESS
>
>Date sent: Fri, 27 Apr 2001 15:55:21 +0100 (BST)
>From: Maria Quinn <[EMAIL PROTECTED]>
>To: "Sterin, Ilya" <[EMAIL PROTECTED]>
>Copies to: "'mi email '" <[EMAIL PROTECTED]>,
> "'[EMAIL PROTECTED] '" <[EMAIL PROTECTED]>
>Subject: RE: How to add Date records to MS ACCESS
>
> > On Fri, 27 Apr 2001, Sterin, Ilya wrote:
> >
> > > I believe in access you must enter date as "#02/03/1999#"
> > >
> > > Also your query is totally wrong, it's not
> > > "INSERT $date INTO table1
> > > VALUES($date)"
> > >
> > > but rather
> > >
> > > "insert into table 1 date values ($date)
> >
> > Shouldn't that be:
> >
> > insert into table1 values ($date)
> >
> > I'm not familiar with Access SQL syntax, so I may be wrong.
> >
> > Maria
>
>"insert into table1 date values ($date)"
>
> >is probably closest to what was meant by mi email. Ilya's version
> >contained just a typo (the space between 'table' and '1'). BTW, what
> >about storing your dates as long integers and leaving the conversion
> >from input and to formatted output to the application level ?
>
>Wooppss, typing too fast, sorry.
>
>
>
> >Bodo
> >[EMAIL PROTECTED]
> >Dr. med. Bodo Eing
> >Institut fuer Medizinische Mikrobiologie
> >Klinische Virologie
> >v.-Stauffenbergstr. 36
> >48151 Muenster
> >Germany
>
>Phone: ++49 251 7793 111 Fax: ++49 251 7793-104
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Paul Anton Chernoch
Zeborg, Inc.
500 West Cummings Park
Woburn, MA 01801
t: 781.759.0105
f: 781.935.5090
c: 781.606.7382
http://www.zeborg.com
This message is a personal and confidential communication. If you are not
the intended recipient, please do not read, copy, or use it, and do not
disclose it to others. Please notify the sender of the delivery error by
replying to this message, and then delete it from your system. Thank you.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
