Dates and Databases

[grw]

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]

Re: Dates and Databases

I would use YYYY/MM/DD

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

Cheers
Ken

"grw" <none@none.co m> wrote in message
news:OGh4Jb6lDH A.2528@TK2MSFTN GP10.phx.gbl...
: 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!
:
:
:
:
:

[Peter Foti]

Re: Dates and Databases

"grw" <none@none.co m> wrote in message
news:OGh4Jb6lDH A.2528@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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?[/color]

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]

Re: Dates and Databases

Tried both methods and similar errors unfortunately Peter.


"Peter Foti" <peterf@systoli cnetworks.com> wrote in message
news:vpafs5qo0q lm53@corp.super news.com...[color=blue]
> "grw" <none@none.co m> wrote in message
> news:OGh4Jb6lDH A.2528@TK2MSFTN GP10.phx.gbl...[color=green]
> > 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[/color][/color]
valid[color=blue][color=green]
> > in all situations, or am I doing something wrong in the first step?[/color]
>
> 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
>
>[/color]

[Bob Barrows]

Re: Dates and Databases

grw wrote:[color=blue]
> 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![/color]

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]

Re: Dates and Databases

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


"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:eCbh9y#lDH A.1884@TK2MSFTN GP09.phx.gbl...[color=blue]
> grw wrote:[color=green]
> > 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![/color]
>
> Access, actually Jet, uses non-standard language in its JetSQL. For[/color]
example,[color=blue]
> 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[/color]
acceptable[color=blue]
> format for dates is US format: m/d/yyyy. However, we have discovered that[/color]
it[color=blue]
> 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.
>
>[/color]