Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Dates and Databases

Reply
Thread Tools

Dates and Databases

 
 
grw
Guest
Posts: n/a
 
      10-21-2003
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!





 
Reply With Quote
 
 
 
 
Ken Schaefer
Guest
Posts: n/a
 
      10-21-2003
I would use YYYY/MM/DD

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

Cheers
Ken

"grw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
: 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!
:
:
:
:
:


 
Reply With Quote
 
 
 
 
grw
Guest
Posts: n/a
 
      10-21-2003
Tried both methods and similar errors unfortunately Peter.


"Peter Foti" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "grw" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      10-21-2003
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.


 
Reply With Quote
 
grw
Guest
Posts: n/a
 
      10-23-2003
Interesting thanks Bob
I guess, apart from the delimiters, this would upscale to SQL just as well.


"Bob Barrows" <(E-Mail Removed)> wrote in message
news:eCbh9y#(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to use dates earlier than 1900 (Time library says out of range for dates < 1900) me@benjaminarai.com Ruby 1 07-17-2007 02:25 PM
Dates dates dates dates... SQL and ASP.NET David Lozzi ASP .Net 1 09-30-2005 02:18 PM
[podcast] Expert panel discussion of XQuery, native XML databases, SQL/XML databases Ken North XML 0 07-14-2005 05:50 AM
Dates! Dates! Dates! PW ASP General 4 08-09-2004 04:42 PM
Dates and databases grw ASP General 1 10-21-2003 07:42 AM



Advertisments