CODEWITHSUNDEEP
sqloracle-database
user2402107
user2402107

Reputation: 913

Working with dates in Oracle SQL

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.

Table: monthlyBill

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*/

or

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

Upvotes: 2

Views: 297

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Assuming that BILLINGDATE is a DATE column:

I recommend avoiding the use of the TO_CHAR or EXTRACT function in a case like this, as they're likely to force a full table scan - perhaps not an issue for small databases and tables, but perhaps very important when querying a large table. Instead I suggest getting used to using a BETWEEN comparison for handling date ranges - something similar to the following:

SELECT ELECTRICITYUSAGE, WATERUSAGE
  FROM MONTHLYBILL
  WHERE ACCOUNTNUMBER = '211' AND
        BILLING_DATE BETWEEN TO_DATE('01-DEC-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
                         AND TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

It's important to remember that in Oracle a DATE column is actually a timestamp and so there is always a date and a time component to it (accurate only down to seconds, not milli- or micro-seconds), so you always need to take the hours/minutes/seconds into account when dealing with Oracle DATEs.

Best of luck.

Upvotes: 0

user330315
user330315

Reputation:

Assuming that billingdate is a DATE column.

You can't compare a DATE with a string value. If you only want to compare "parts" of a date you need to convert the date to a string: 

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber ='211' 
  and to_char(billingDate,'MM-YY') = '12-12'

But I would strongly recommend to always use four digit years:

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

or use the extract function:

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

To get the previous year, subtract a year, but you need to take into account that in Oracle a DATE always contains a time as well (despite the name of the data type). To set the time to 00:00:00 use trunc()

select electrcityUsage, waterUsage 
from monthlyBill
where accountNumber ='211' 
  and trunc(billingdate) = trunc(sysdate - interval '1' year);

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

One option uses TO_CHAR:

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

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

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)

Upvotes: 4

Related Questions