Dates and databases

[grw]

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

YYYYMMDD hh:mm:ss

[grw]

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

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]

Sorry - quick fingers, ignore this one

"grw" <none@none.com> wrote in message
news:uIbHSY6lDHA.3504@TK2MSFTNGP11.phx.gbl...

> [url]http://www.aspfaq.com/show.asp?id=2260[/url]
>
> YYYYMMDD hh:mm:ss
>
>
>
>
>

[Ken Schaefer]

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.com> wrote in message
news:OGh4Jb6lDHA.2528@TK2MSFTNGP10.phx.gbl...
: [url]http://www.aspfaq.com/show.asp?id=2260[/url]
:
: 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]

"grw" <none@none.com> wrote in message
news:OGh4Jb6lDHA.2528@TK2MSFTNGP10.phx.gbl...

> [url]http://www.aspfaq.com/show.asp?id=2260[/url]
>
> 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]

Tried both methods and similar errors unfortunately Peter.


"Peter Foti" <peterf@systolicnetworks.com> wrote in message
news:vpafs5qo0qlm53@corp.supernews.com...

> "grw" <none@none.com> wrote in message
> news:OGh4Jb6lDHA.2528@TK2MSFTNGP10.phx.gbl...

> > [url]http://www.aspfaq.com/show.asp?id=2260[/url]
> >
> > 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
>
>

[Bob Barrows]

grw wrote:

> [url]http://www.aspfaq.com/show.asp?id=2260[/url]
>
> 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]

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


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eCbh9y#lDHA.1884@TK2MSFTNGP09.phx.gbl...

> grw wrote:

> > [url]http://www.aspfaq.com/show.asp?id=2260[/url]
> >
> > 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.
>
>