Dateadd in SQL statement not working

[Brandon]

Greetings,

I am having a problem getting a SQL statement that is functional in
Access 2K to work in ASP.

My Access SQL statement is this:

SELECT DateAdd('s',Ticket_Opened_Date,'Dec 31, 1969 8:00:00PM') AS
Opened_Date, AL.Last_Significant_Update_Date AS Last_Updated,
AL.Due_Date AS Ticket_Due_Date, AL.Managing_Station,
AL.Ticket_Opened_Date, AL.Short_Description, AL.Source, AL.Comments,
AL.Member_Visible_Outage_, AL.Impact_Comments, AL.Ticket__,
AL.External_Ticket__, AL.Last_Significant_Update_Date, AL.Due_Date,
AL.Status
FROM dbo_AL_Problem_Management AS AL
WHERE (((AL.Managing_Station)="NOC/ALnet" Or (AL.Managing_Station)="
IOC") AND ((AL.Status)<>4));

The error message from the browser is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MERANT][ODBC Sybase driver][SQL Server]Incorrect syntax near AL.

The SQL statement in ASP is:

sql = "SELECT DateAdd('s',Ticket_Opened_Date,'Dec 31, 1969 8:00:00PM')
AS Opened_Date, AL.Last_Significant_Update_Date AS Last_Updated,
AL.Due_Date AS Ticket_Due_Date, AL.Managing_Station,
AL.Ticket_Opened_Date, AL.Short_Description, AL.Source, AL.Comments,
AL.Member_Visible_Outage_, AL.Impact_Comments, AL.Ticket__,
AL.External_Ticket__, AL.Last_Significant_Update_Date, AL.Due_Date,
AL.Status
FROM dbo_AL_Problem_Management AS AL
WHERE (((AL.Managing_Station)="NOC/ALnet" Or (AL.Managing_Station)="
IOC") AND ((AL.Status)<>4)) "

Any help would be appreciated.

Thank you,

Brandon

 

[Bob Barrows [MVP]]

Greetings,

I am having a problem getting a SQL statement that is functional in
Access 2K to work in ASP.

My Access SQL statement is this:

SELECT DateAdd('s',Ticket_Opened_Date,'Dec 31, 1969 8:00:00PM') AS

In VB/VBA/VBScript, you have to surround the datepart argument (the first
argument) with quotes. Not so in Transact-SQL. Remove the quotes from the
's'.

You may wish to consider using a more international-aware date format. While
you may be thinking that you are eliminating the month/day confusion by
using the name of the month, you may run into interesting results on servers
set up for different languages. The safest date format to use in
Transact-SQL is the ISO format: YYYYMMDD hh:mm:ss

Bob Barrows

 

[Brandon]

Bob,

Thank you very much for your reply. I will remove the ticks and let you
know
what happens. I do have another question abou the date formatting
though.

The only reason I used the date syntax the way I did is because the DBA
recommended it that way. Should I be able to use your suggestion (as
date
formats can vary) or is the method suggested by the DBA the way the db
is
setup?

Thanks again,
Brandon Dreiling

 

[Bob Barrows [MVP]]

Bob,

Thank you very much for your reply. I will remove the ticks and let
you know
what happens. I do have another question abou the date formatting
though.

The only reason I used the date syntax the way I did is because the
DBA recommended it that way. Should I be able to use your suggestion
(as date
formats can vary) or is the method suggested by the DBA the way the db
is
setup?

All I can do is repeat what i said in my previous message:
The safest date format to use in
Transact-SQL is the ISO format: YYYYMMDD hh:mm:ss

This format will be recognized by SQL Server regardless of how the server is
set up.
http://www.aspfaq.com/show.asp?id=2260

HTH,
Bob Barrows

 

[Brandon]

As a follow-up to my date conversion dilema, I am still unable to get
DateAdd
to work correctly.

I have removed ticks from around the "s" and I have attempted to enclose
19691231 20:00:00 in ticks and also not enclosed; I have tried using
colons
to seperate the hh:mm:ss and I have tried without colons; I tried with a
space
between the date/time, I tried without space. I am still receiving the
same browser error message

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

Line 22 happens to be my sql statement as below

sql = "SELECT DateAdd(s,Ticket_Closed_Date,19691231 20:00:00) AS
Closed_Date, DateAdd(s,Ticket_Opened_Date,19691231 20:00:00) AS
Opened_Date, DateAdd(s,Last_Significant_Update_Date,19691231 20:00:00)
AS Last_Updated, DateAdd(s,Due_Date,19691231 20:00:00) AS
Ticket_Due_Date, AL.Managing_Station, AL.Status, AL.Short_Description,
AL.Source, AL.Comments, AL.Member_Visible_Outage_,
AL.Impact_Comments, AL.Ticket__, AL.External_Ticket__,
AL.Last_Significant_Update_Date,
DateDiff(s,Ticket_Closed_Date,Ticket_Opened_Date) AS Length_Opened FROM
dbo.AL_Problem_Management AS AL WHERE (((AL.Managing_Station)='NOC/
ALnet' Or (AL.Managing_Station)='NOC/IOC') AND ((AL.Status)<>4)) "

Any other help is greatly appreciated.

Thanks,
Brandon Dreiling

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

The thought just occurred to me that you may be attempting to use a linked
table in your Jet database (something I do not recommend doing: you should
connect directly to the Sybase database from ASP rather than taking the
circuitous route through the Access linked table), but this raises doubts:

FROM dbo.AL_Problem_Management AS AL

This is not legal syntax for referring to a table in JetSQL, whether it's
linked or not. There is no dbo or any other database owner in a Jet
database, so Jet does not support multipart names for its tables. So I ask:
is this query which you say works in Access from a passthrough query?

Bob Barrows

 

[Brandon]

Bob,

I am connecting to a Sybase db, however, I am using Access to write and
test
my queries.

In the FROM line, I am able to use FROM db.whatever.blah AS db to
shorten
the code used in every other line. I have tested that query without the
dateadd piece and it works without a problem (tested on an ASP page).

I don't have any problems with the connection script or returning basic
data
from the db. I just seem to have difficulty using DateAdd.

It has been a difficult search for assistance because I am working with
both
ASP and SQL (Sybase) and there seems to be little in the way of advice
for
both.

Thanks for all of your help, I will look for a good Sybase forum.

Brandon

 

[Bob Barrows [MVP]]

Bob,

I am connecting to a Sybase db, however, I am using Access to write
and test
my queries.

In the FROM line, I am able to use FROM db.whatever.blah AS db to
shorten
the code used in every other line.

No, I was not questioning the "AS db" portion. I was questioning your
ability to run a query containing "FROM dbo.tablename" in Access. This
syntax should fail unless it's a passthrough query.

I have tested that query without
the dateadd piece and it works without a problem (tested on an ASP
page).

You're really confusing me. I thought you said you had tested your queries
_in Access_ before attempting to run them in ASP!

I don't have any problems with the connection script or returning
basic data
from the db. I just seem to have difficulty using DateAdd.

It has been a difficult search for assistance because I am working
with both
ASP and SQL (Sybase) and there seems to be little in the way of advice
for
both.

You don't need both. You need to learn how write queries in Sybase's version
of SQL, in this case how to use DateAdd in a Sybase query. Once you have
that, then it's simply a matter of running the query from ASP, which does
not differ from one database to another. If Sybase has a query execution
tool, you should use that to design, create and debug your queries before
attempting to run the queries from ASP (or any client application for that
matter).

Thanks for all of your help, I will look for a good Sybase forum.

FWIW, I would eschew the dynamic sql approach you are taking and switch to
using stored procedures, which I am pretty sure that Sybase supports. For
performance reasons, but mainly for security reasons:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

HTH,
Bob Barrows

 

[Brandon]

Bob,

Thanks once again for all of your help. I finally figured out the
DateAdd statement for Sybase. Instead of using an s to represent
seconds, writing out second was proper.

DateAdd(second, .... is the proper syntax.

I now have a functioning query!

Thanks,
Brandon