oracle extract

最近在把ASP代码翻译成aspx，总是碰到原sql错误。不熟悉oracle sql的我真是郁闷啊~~

oracle中没有YEAR或者MONTH函数

需要在SQL语句中实现截取date类型的，可使用extract函数

原SQL：

 sqlMonthFound="SELECT DISTINCT out_date AS 日期, out_1 / 1.05 AS 销货收入, out_17 AS 来客数,(out_1 / out_17) / 1.05 AS 客单价 FROM account WHERE (store_id = '"&store_id&"') AND MONTH(out_date) = MONTH('"&yearnow&"') AND YEAR(out_date) = YEAR('"&yearnow&"') ORDER BY out_date ASC"

修正后：

SELECT DISTINCT out_date AS days,round(out_1 / 1.05,2) AS out_1,out_17 AS out_17,round((out_1 / out_17) / 1.05,2) AS out_1_17 FROM account
WHERE (store_id = '4108') --//para
AND extract(MONTH from out_date) ='9'--//Para
AND extract(YEAR from out_date) = '2006'--//Para
ORDER BY out_date ASC