Working with dates in Oracle SQL

Question

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

Where in the Oracle manual did you find dateadd() or getdate() — user330315
– user330315, Commented Dec 17, 2016 at 14:18
Are you using Oracle or SQL Server? The syntax is SQL Server not Oracle. — Gordon Linoff
– Gordon Linoff, Commented Dec 17, 2016 at 14:28
I am aware if that, I am using Oracle. I am ver new to SQL in general — user2402107
– user2402107, Commented Dec 17, 2016 at 14:29

Tim Biegeleisen · Accepted Answer · 2016-12-17 14:30:42Z

4

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)

edited Dec 17, 2016 at 14:30

answered Dec 17, 2016 at 14:19

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2402107 Over a year ago

and how would you do the exact same thing but minus one year?

Tim Biegeleisen Over a year ago

Do you want the year 2011 hard coded, or do you want one year before the current year?

user2402107 Over a year ago

one year before whatever the WHERE statement would say,

score 2 · Accepted Answer · 2016-12-17 14:31:30Z

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);

Bob Jarvis - Слава Україні · Accepted Answer · 2016-12-17 15:04:25Z

0

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.

answered Dec 17, 2016 at 15:04

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

50.2k10 gold badges81 silver badges119 bronze badges

3 Comments

user330315 Over a year ago

I highly recommend to not use month names in to_date() - the correct parsing of the names depends on the client's language setting. Your query would fail on my computer.

Bob Jarvis - Слава Україні Over a year ago

I understand your point but respectfully disagree. 11-DEC-2013 is unambiguous. 11-12-13 is...what? Plus DD-MON-YYYY is US military format. Got used to the logic of it it 30+ years ago. Never looked back. Maybe we could standardize on the Esperanto month names? Looks like I'd need to get used to two or three differences from the English abbreviations - seems to be roughly the same for German IIRC. YM will undoubtedly V. :-)

user330315 Over a year ago

Well as I said: 11-DEC-2013 will fail if run from a client that spells December differently (German, Spanish, Italian, Polish and a lot more) - unambiguous or not. The problem is that this depends on the client not the server. It's better to use a format that writes the month as a number and if you provide a format mask for the to_date() function (which you always should do anyway) it's not ambiguous. e,g. to_date(01-12-2012', 'dd-mm-yyyy')` I typically use international ISO format e.g. 2012-12-01 (of course with a format mask)

Collectives™ on Stack Overflow

Working with dates in Oracle SQL

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related