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]

I would use YYYY/MM/DD

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

Cheers
Ken

: 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]

Tried both methods and similar errors unfortunately Peter.

 

[Bob Barrows]

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

 

[grw]

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