oracle extract

最新推荐文章于 2024-10-17 18:58:27 发布

diukuijian4440

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量106

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Jersen/p/SQL.html

版权

最近在把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

转载于:https://www.cnblogs.com/Jersen/p/SQL.html