How to query a odbc datasource with field named date

jeisma · Post by **jeisma** » Mon Mar 20, 2017 12:34 pm

hi,

my odbc datasource has a field with name "date". I get error "... Syntax error in SQL statement at or about "date FROM PUB.my_table" (10713)

i tried to manually the query using several combinations like:

SELECT my_table.date FROM PUB.my_table
SELECT [date] FROM PUB.my_table

tried, SELECT 'date' FROM PUB.my_table - but it threw me a separate batch of errors, it seems that the value is being converted to character as being read, and to date again before being written.
Any ideas how to do this?

Thanks!

PaulVL · Post by **PaulVL** » Mon Mar 20, 2017 3:53 pm

Slap your source system DBA team with so many lefts they will be begging for a right.

There are certain key words that should not be used for column names in databases. I am surprised that they would have allowed that.

If they allowed the "date" column name, ask them for the correct syntax for the SQL statements to extract data. Have them give you an example of it running.

chulett · Post by **chulett** » Mon Mar 20, 2017 3:57 pm

What database is this, exactly? For SQL Server, the square brackets [date] syntax should have worked or at least that's my understanding. But the rules for reserved words change from DB to DB so it would be good to know for certain which one it was.

And yes, a stern finger-wagging is in order for whomever chose that field name.

jeisma · Post by **jeisma** » Mon Mar 20, 2017 4:10 pm

It's a Progress RDBMS database. i would personally would have named it according to the purpose of the field (ie. trans_date, po_date, etc).

I may be able argue to rename that field if indeed there is no way out of the error.

And avoiding the finger-wagging and slapping

I'd ask the rational why they used that field name.

jeisma · Post by **jeisma** » Tue Mar 21, 2017 9:18 am

hi,

that actually worked!

thank you!

(i would still argue to rename that field though..