Dates and Databases

Discussion in 'ASP General' started by grw, Oct 21, 2003.

  1. grw

    grw Guest

    http://www.aspfaq.com/show.asp?id=2260

    Inserting into an Access database using the above script generates this
    error :
    Microsoft JET Database Engine error '80040e07'
    Syntax error in date in query expression '#20031021 20:36#'.

    Using SQL : UsersTable SET UpdateTime=#"& dbDate(Now())&"# .....

    If YYYYMMDD hh:mm:ss is a universal date, why is it rejecting it?

    If I change it to YYYY/MM/DD hh:mm:ss it then becomes valid. Is this valid
    in all situations, or am I doing something wrong in the first step?

    Cheers!
     
    grw, Oct 21, 2003
    #1
    1. Advertising

  2. grw

    Ken Schaefer Guest

    I would use YYYY/MM/DD

    I have used that with both Access/Jet, and SQL Server with plenty of
    success.

    Cheers
    Ken

    "grw" <> wrote in message
    news:...
    : http://www.aspfaq.com/show.asp?id=2260
    :
    : Inserting into an Access database using the above script generates this
    : error :
    : Microsoft JET Database Engine error '80040e07'
    : Syntax error in date in query expression '#20031021 20:36#'.
    :
    : Using SQL : UsersTable SET UpdateTime=#"& dbDate(Now())&"# .....
    :
    : If YYYYMMDD hh:mm:ss is a universal date, why is it rejecting it?
    :
    : If I change it to YYYY/MM/DD hh:mm:ss it then becomes valid. Is this valid
    : in all situations, or am I doing something wrong in the first step?
    :
    : Cheers!
    :
    :
    :
    :
    :
     
    Ken Schaefer, Oct 21, 2003
    #2
    1. Advertising

  3. grw

    grw Guest

    Tried both methods and similar errors unfortunately Peter.


    "Peter Foti" <> wrote in message
    news:...
    > "grw" <> wrote in message
    > news:...
    > > http://www.aspfaq.com/show.asp?id=2260
    > >
    > > Inserting into an Access database using the above script generates this
    > > error :
    > > Microsoft JET Database Engine error '80040e07'
    > > Syntax error in date in query expression '#20031021 20:36#'.
    > >
    > > Using SQL : UsersTable SET UpdateTime=#"& dbDate(Now())&"# .....
    > >
    > > If YYYYMMDD hh:mm:ss is a universal date, why is it rejecting it?
    > >
    > > If I change it to YYYY/MM/DD hh:mm:ss it then becomes valid. Is this

    valid
    > > in all situations, or am I doing something wrong in the first step?

    >
    > I have not tried this myself, but if you include the "time designator"
    > character (as specified in ISO 8601), will it work then?
    >
    > Basic: YYYYMMDDThhmmss
    > Ex - 20031021T100900
    >
    > Extended: YYYY-MM-DDThh:mm:ss
    > Ex - 2003-10-21T10:09:00
    >
    > Regards,
    > Peter Foti
    >
    >
     
    grw, Oct 21, 2003
    #3
  4. grw

    Bob Barrows Guest

    grw wrote:
    > http://www.aspfaq.com/show.asp?id=2260
    >
    > Inserting into an Access database using the above script generates
    > this error :
    > Microsoft JET Database Engine error '80040e07'
    > Syntax error in date in query expression '#20031021 20:36#'.
    >
    > Using SQL : UsersTable SET UpdateTime=#"& dbDate(Now())&"# .....
    >
    > If YYYYMMDD hh:mm:ss is a universal date, why is it rejecting it?
    >
    > If I change it to YYYY/MM/DD hh:mm:ss it then becomes valid. Is this
    > valid in all situations, or am I doing something wrong in the first
    > step?
    >
    > Cheers!


    Access, actually Jet, uses non-standard language in its JetSQL. For example,
    using # to delimit dates is non-standard. The format required for dates is
    also non-standard. If you believe the online help, then the only acceptable
    format for dates is US format: m/d/yyyy. However, we have discovered that it
    will also handle dates in yyyy-mm-dd and yyyy/mm/dd formats. The ISO
    standard format that you attempted to use is unfortuantely not handled.

    For your specific purpose, JetSQL has access to many VBA functions,
    including Date(), Now(), and Time(), so you do not need to concatenate the
    values from those functions into your SQL statements. You can use the
    function calls themselves:

    Update UsersTable SET UpdateTime=Now()

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Oct 21, 2003
    #4
  5. grw

    grw Guest

    Interesting thanks Bob
    I guess, apart from the delimiters, this would upscale to SQL just as well.


    "Bob Barrows" <> wrote in message
    news:eCbh9y#...
    > grw wrote:
    > > http://www.aspfaq.com/show.asp?id=2260
    > >
    > > Inserting into an Access database using the above script generates
    > > this error :
    > > Microsoft JET Database Engine error '80040e07'
    > > Syntax error in date in query expression '#20031021 20:36#'.
    > >
    > > Using SQL : UsersTable SET UpdateTime=#"& dbDate(Now())&"# .....
    > >
    > > If YYYYMMDD hh:mm:ss is a universal date, why is it rejecting it?
    > >
    > > If I change it to YYYY/MM/DD hh:mm:ss it then becomes valid. Is this
    > > valid in all situations, or am I doing something wrong in the first
    > > step?
    > >
    > > Cheers!

    >
    > Access, actually Jet, uses non-standard language in its JetSQL. For

    example,
    > using # to delimit dates is non-standard. The format required for dates is
    > also non-standard. If you believe the online help, then the only

    acceptable
    > format for dates is US format: m/d/yyyy. However, we have discovered that

    it
    > will also handle dates in yyyy-mm-dd and yyyy/mm/dd formats. The ISO
    > standard format that you attempted to use is unfortuantely not handled.
    >
    > For your specific purpose, JetSQL has access to many VBA functions,
    > including Date(), Now(), and Time(), so you do not need to concatenate the
    > values from those functions into your SQL statements. You can use the
    > function calls themselves:
    >
    > Update UsersTable SET UpdateTime=Now()
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    grw, Oct 23, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    874
    Rob Schieber
    Sep 30, 2005
  2. Ken North
    Replies:
    0
    Views:
    609
    Ken North
    Jul 14, 2005
  3. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    201
    Mark Schupp
    Aug 9, 2004
  4. Replies:
    1
    Views:
    207
    Jano Svitok
    Jul 17, 2007
  5. kellygreer1

    RFC-822 dates into Ruby dates

    kellygreer1, Jun 8, 2008, in forum: Ruby
    Replies:
    1
    Views:
    204
    Eric I.
    Jun 8, 2008
Loading...

Share This Page