Excel date parameter in SQL query

问题:

I use an SQL query to retrieve a data table from an oracle database with excel.

When a fixed date is used I get this request:

SELECT * FROM MyTable WHERE MyTable.Date > {ts '2014-09-30 00:00:00'}

What I would like to do is replace the fixed date by a date from a cell. I tried the folowing:

SELECT * FROM MyTable WHERE MyTable.Date > ?
SELECT * FROM MyTable WHERE MyTable.Date > {ts ?}
SELECT * FROM MyTable WHERE MyTable.Date > {ts '?'}
SELECT * FROM MyTable WHERE MyTable.Date > {ts [?]}
SELECT * FROM MyTable WHERE MyTable.Date > {ts ['?']}

None of it worked. I don't know where to go from there. In the end I'd like the date cell to work in either american (12/31/2014) or international (31/12/2014) configuration.

回答1:

If the date is stored in the cell A1 using Excel date format then to pass it as a parameter to the query you must do as follow:

1 - Transform the date format to a correct date string in cell B2 for obdc:

= LOWER(TEXT(D2,"dd-mmmm-yyyy"))

2 - Pass the B2 cell as a paremeter with the query:

SELECT * FROM MyTable WHERE MyTable.Date > ?