Re: Dateadd in SQL statement not working

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 05/09/04 

Date: Sun, 9 May 2004 09:38:32 -0400

> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [MERANT][ODBC Sybase driver][SQL Server]Incorrect syntax near 'AL'.
> /page.asp Line 22

Omigod, I thought you said you were using SQL Server, which is what my
advice applied to. Upon reading this error message from the Sybase ODBC
driver (????) I went back to reread your initial message and saw " ... SQL
statement that is functional in Access 2K to work in ASP."

Due to a sloppy initial reading, I missed the "Access" part of your
question, so allow me to revise my answer so it applies to JetSQL (the
version of SQL used by Jet, which is the database usually used by Access):

JetSQL uses the VBA version of DateAdd, so you do need to delimit the
datepart argument. There are two further problems: JetSQL requires date
literals to be delimited by hash marks (#). Also, JetSQL does not recognize
the ISO date format I recommended in my initial message. The "safe" format
that Jet will always recognize is YYYY-MM-DD, so if the database is Jet, the
statement should look like this:

SELECT DateAdd('s', Ticket_Opened_Date, #1969-12-31 20:00:00#) ...

But my confusion is not totally resolved: Why are you using a Sybase ODBC
driver to connect to either a Jet or a SQL Server database? Are you really
connecting to a Sybase database? If so, I have no idea whether either of my
answers applies to a Sybase database. Please clarify what you are doing
here. If you need help with Sybase SQL syntax, you need to find a Sybase
newsgroup or forum, which you probably will not find on the MS public
newsgroup hierarchy. Google can help you find a Sybase newsgroup.

If you are not using a Sybase database, then you need to use the appropriate
OLEDB provider for whatever database you are using. See
www.able-consulting.com/ado_conn.htm for help with the appropriate
connection string.

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • RE: Import from Sybase SQL Anywhere 9
    ... I found similar issue reported and it seems to be caused by olddb driver ... searching the Sybase newsgroups for information on configuring the Sybase ... ianywhere.com support staff. ... Migrating a Microsoft Access Application into a .NET Solution that Uses SQL ...
    (microsoft.public.sqlserver.dts)
  • Re: SAP direct JDBC access
    ... > data with JDBC. ... > SQL Server. ... you can use a data source to hide the actual database ... driver used. ...
    (comp.lang.java.programmer)
  • Re: Sybase to Access
    ... would be willing to accept SQL 2005 _CRAP_ that is built into VB 2005. ... supported on TRANSMETA, ... I've had apps with an Access database that was 500MB, ... Access could use link tables to link to Sybase perhaps and ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Combine Recordsets
    ... What do you mean by an 'in-house' OleDb driver? ... can access the same database with a number of different drivers of your ... using an SQL string involving 'union', to get data from two different ...
    (microsoft.public.vb.general.discussion)
  • Re: How to write your own database driver
    ... There's a software which connects to a database and reads data from the ... So I thought maybe I can write a custom driver. ... the first 28 characters of the string, or could I make a similar ... Your question is more appropriate for an ODBC, OLEDB or sybase newsgroup. ...
    (comp.databases.theory)