在 Oracle SQL 中处理日期 [英] Working with dates in Oracle SQL

问题描述

我对使用 SQL 很陌生，我尝试做的是仅使用月份和年份选择 waterUsage 和electricrcityUsage，然后选择上一年的 waterUsage 和electricrcityUsage.

I am very new when it comes to using SQL and what I am attempting to do is select the waterUsage and electrcityUsage using only the month and year and select the waterUsage and electrcityUsage from the previous year.

但是，我似乎无法找出使用日期来完成这项工作的适当方法.

However I cannot seem to figure out the appropriate way to use dates in order to make this work.

表格:每月账单

1. billingDate 01-SEP-15
2. waterUsage varchar(256)
3. electrcityUsage varchar(256)
4. accountNumber varchar(256)

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber ='211' 
  and billingDate = '12-12' /*,month, year*/

或

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber ='211' 
  and billingDate = DATEADD(year,-1,GETDATE()); /*,previous year*/

推荐答案

一个选项使用TO_CHAR:

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber = '211' and
     to_char(billing_date, 'MM-YYYY') = '12-2012'

这假设您实际上使用的是 Oracle，而不是 SQL Server.

This assumes that you're actually using Oracle, and not SQL Server.

如果你想要 2012 和 2011 然后继续往 WHERE 子句中添加另一个条件.在这种情况下，我可能会使用 EXTRACT:

If you wanted 2012 and 2011 then just go ahead and add another condition to the WHERE clause. I might use EXTRACT in this case:

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber = '211' and
    extract(month from billingDate) = 12 and
    extract(year from billingdate) in (2011, 2012)

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