Dateadd in SQL statement not working

Discussion in 'ASP General' started by Brandon, May 7, 2004.

  1. Brandon

    Brandon Guest

    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

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brandon, May 7, 2004
    #1
    1. Advertising

  2. Brandon wrote:
    > 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

    --
    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"
    Bob Barrows [MVP], May 7, 2004
    #2
    1. Advertising

  3. Brandon

    Brandon Guest

    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

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brandon, May 7, 2004
    #3
  4. Brandon wrote:
    > 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

    --
    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"
    Bob Barrows [MVP], May 8, 2004
    #4
  5. Brandon

    Brandon Guest

    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


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brandon, May 9, 2004
    #5
  6. > 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"
    Bob Barrows [MVP], May 9, 2004
    #6
  7. 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
    --
    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"
    Bob Barrows [MVP], May 9, 2004
    #7
  8. Brandon

    Brandon Guest

    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

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brandon, May 10, 2004
    #8
  9. Brandon wrote:
    > 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
    --
    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.
    Bob Barrows [MVP], May 10, 2004
    #9
  10. Brandon

    Brandon Guest

    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



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brandon, May 11, 2004
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt Tapia

    DateAdd Function and error BC30451

    Matt Tapia, Aug 13, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    2,153
    Chris R. Timmons
    Aug 13, 2003
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    433
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    443
    David Browne
    Aug 21, 2003
  4. lofty

    DateAdd does not work

    lofty, Jul 20, 2003, in forum: ASP General
    Replies:
    5
    Views:
    160
    Chris Barber
    Jul 20, 2003
  5. Gabe

    Dateadd function not adding up...

    Gabe, Oct 30, 2003, in forum: ASP General
    Replies:
    3
    Views:
    135
    Ray at
    Oct 31, 2003
Loading...

Share This Page