SQL : referencing a field called 'date'

[LeBlatt]

I have a database where the date fields are just called "date". Since this is a reserved word, when referencing these fields I either quote the name, like

myTcursor."date" = date ()

or use the numbered notation.

But how can I access them in SQL ?
SELECT NAME, DATE FROM MYTABLE causes an error,
and trying ether 'NAME' or "NAME" creates a column called NAME containing the value NAME in each row.
I cannot rename the field without major problems, is there any workaround ? Thanks a lot.

 

[footpad]

LeBlatt,

Here's an example based on the Contacts sample table:

SELECT DISTINCT
   c.Name, c."Date", c."Customer No" AS ID
FROM
   "contacts.DB" c
WHERE
   (c."State/Prov" = 'HI')
ORDER BY
   c."Date" DESC, c."Customer No", c.Name

It shows a couple of different tricks, including how to select a field using a SQL keyword, which as you know, is something you really should avoid when naming fields and tables.

BTW, if you know QBE pretty well, you can use it to get a start on a SQL query by creating the initial query in QBE and choosing View | Show SQL Code. Paradox will create a functional SQL query from your QBE. This doesn't work for all QBE queries, but I've only run into a handle that can't be translated this way.

Be aware that the translated SQL tends to be more "wordy" than strictly necessary, but at least it shows the basics.

Hope this helps...

-- Lance

 

[LeBlatt]

It helped a lot, thx !