SQL查询中的Excel日期参数 [英] Excel date parameter in SQL query

问题描述

我使用SQL查询通过excel从oracle数据库中检索数据表.

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 ['?']}

没有一个有效.我不知道从那里去哪里. 最后，我希望日期单元在美国(2014年12月31日)或国际(2014年12月31日)配置下工作.

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.

推荐答案

如果使用Excel日期格式将日期存储在单元格A1中，则要将其作为参数传递给查询，您必须执行以下操作:

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-将日期格式转换为obdc的单元格B2中的正确日期字符串:

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

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

2-通过查询传递B2单元作为参数:

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

SELECT * FROM MyTable WHERE MyTable.Date > ?

这篇关于SQL查询中的Excel日期参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！