Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: ODBC Sql Server Query Error

    Hi,

    I'm attempting to use the SQLExecute function to make a query to a SQL Server DB through ODBC.

    However, the column I am trying to retrieve is called "date". When I try and query, I get back the message, "S0022: Invalid column name 'date'".

    I believe I'm getting the error because date is a sql keyword (however if I run the query directly through SQL Interactive, I do not get a problem).

    If anyone knows how I can get around this problem, that would be very helpful, as it is not possible to change the column name.

    Thanks in advance.
    Khalid.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use brackets around the offending columns. Something like:
    Code:
    SELECT [date] FROM naughtyTable
    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    7
    Gave that a try -- gives the same error. Any other ideas?

    Thanks,
    Khalid.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it with doublequotes

    Code:
    select "date" from naughtyTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could also try functionizing©®™ it
    Code:
    select dateadd(d,0,[date]) as mydate from naughtyTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    That will just return "date" for every row.

    Post your query.

    I have a sneaky suspicion something else is going on here like maybe the column does not exist or there is some imprperly formatted inline SQL.

    Otherwise what Pat said should have worked.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    That will just return "date" for every row.
    wrong

    you may try it for yourself --
    Code:
    create table datetest
    ( id tinyint not null primary key 
    , date datetime
    )
    
    insert into datetest (id, date) values ( 1, getdate() )
    insert into datetest (id, [date]) values ( 2, '11/11/2005' )
    insert into datetest (id, "date") values ( 3, '2005-01-19' )
    
    select id, "date" from datetest
    want to know what this produces?

    1 2005-01-18 15:32:29.163
    2 2005-11-11 00:00:00.000
    3 2005-01-19 00:00:00.000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Alright you win this round Batman.

    I was thinking about single qoutes which would spit out Date for every row. I have to stop posting at this during the day. I start getting a little flitty.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •