Getting Started With Oracle: Date Math

As soon as you store a date value

Someone is going to want to do something with it. Bucket it into a 30/60/90 day window, figure out how many times something happened between it and current, filter on it, or send you creepy anonymous cards on your birthday full of doll hair and coupons for lotion.

Since Oracle is apparently no slouch, it can do all this stuff for you. No more awkwardly counting days on your fingers or messing up your day-at-a-time Far Side desk calendar trying to look 6 months into the future. Not for you, smarty pants. We’re gonna use some SQL.

Just like home

If you add a number to a date value, Oracle adds that many days to the date. It’s exactly the same as GETDATE() + whatever in SQL Server. Below will add and subtract 365 days from the hire date of each employee.

Check out those headers!

Check out those headers!

One thing I really like that Oracle does, that SQL Server doesn’t do, is replace calculated column headers with the syntax behind them. It can be annoying if there’s really long or complicated syntax behind it, but it’s generally nicer to have something there. SQL Server just says “No column name” when you do the same thing. Both scenarios bring their own good reasons to alias columns to the table. Get it? Table. HEH!

One big difference you’ll find is that adding or subtracting two date values in Oracle leaves you with a number, not a date. The number you’re left with is the number of days between the two days.

This is some old sample data.

This is some old sample data.

It’s really easy to get the number or months between two dates, or add months to a date. You can use the MONTHS_BETWEEN and ADD_MONTHS functions to do that.

These are areas where I think SQL Server is a bit ahead of Oracle. DATEADD and DATEDIFF offer you a lot more flexibility and range of motion to pull out or add different intervals. I know there are Oracle workarounds with some additional math operators, but they’re not as straightforward to me.

A couple other neat options are NEXT_DAY and LAST_DAY. Next day tells you the next time the day of the week you choose occurs after the date you pass in, and LAST_DAY gives you the last day of the month for the date you pass in.

The NEXT_DAY function would be useful in a situation where the Monday following someone getting hired, they had to get their picture taken for their badge, or whatever.

SQL Server has EOMONTH (2012+), but curiously, neither platform has FIRST_DAY, or SOMONTH functions, to give you the first day of the month. That seems odd to me considering the amount of syntax that goes into getting back to the first day of the month (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0)).

You can also add years and months to a date using the TO_YMINTERVAL function. Um. Confetti?

Finally, if you need to get individual parts of a date, you can use the EXTRACT function:

This is pretty close to the DATEPART function in SQL Server.

Rudimentary Can’t Fail

Both Oracle and SQL Server store dates and times in the same way, though the way they’re presented and some of the ways we work with them vary between the two platforms. They’re not too far off from each other, and both have strengths and weaknesses. SQL Server makes it really easy to pass in dates, but Oracle is far less ambiguous about which dates it will compare, which can be error prone. Either way, if you’re developing an application to work with either platform, or if you need to port code over, you have your work cut out for you when it comes to working with dates.

Thanks for reading!

Brent says: those headers, are you kidding me? It puts the plain English formula as the field name? That’s amazing.

Previous Post
Getting Started With Oracle: Working With Dates
Next Post
Getting Started With Oracle: Working With Strings

16 Comments. Leave new

  • Søren Kongstad
    February 16, 2016 8:31 am

    You can use the round() function on days also specifying what you want to round on, see http://www.techonthenet.com/oracle/functions/round_date.php

  • TRUNC does a lot of things when working with dates, and usually is simpler than SQLServer. For SOMONTH, hou have:
    select trunc(sysdate, ‘MM’) from dual;

  • Nice series, Erik, thanks! I do like the Clash reference as well.

  • Brian J. Parker
    February 16, 2016 11:26 am

    I don’t know if this is a digression, but, I’ve never worked with a database for long that I didn’t end up putting in a calendar table: one row per date, columns for common functions for the business in question (fiscal period, relative first day of month, flags for whether it is a business day and/or holiday, whatever I need often), indexed up the wazoo since it never changes.

    That doesn’t mean it wouldn’t be nice for Microsoft and Oracle to address relative weaknesses in their products, and your write-up is appreciated if I ever have to work with Oracle; but, a calendar table is usually a great practical help in working around those existing weaknesses, and I love plugging them.

  • Hi

    When is the next Free Training Video due up?

  • The ability to add days to a temporal data type in SQL Server is only possible with datetime and smalldatetime data types. The newer temporal data types (date, time, datetime2, datetimeoffset) are not stored the same, and adding an integer to them errors. And since Microsoft recommends against using datetime and smalldatetime in new code (https://msdn.microsoft.com/en-us/library/ms187819.aspx), it’s probably not a good practice to use.

  • I’ve been working with Sql Server since version 6.5.

    In that time I have had only a couple years of contracts programming web apps with Oracle back end.

    Oracle has 10X better programming environment and programming language in my opinion vs. TSQL.

    Ability to organize code into packages, basically a “project” hierrarchy, better error handling, easier support for handling various data structures. It was no contest…

    ken

    • Søren Kongstad
      February 18, 2016 8:37 am

      I started my developer career doing almost 10 years of PL/SQL on Oracle. Half way through that SQL Server 2005 was installed on my laptop, and I was given the task of finishing af SSIS project a previous consultant had started.
      Now I almost exclusively work with SQL Server – because that is what out customers have, but I must agree that as a developer I liked the tools much better with Oracle. SSMS is slowly getting better, but it is still nowhere near toad or PL/SQL Develop.

      This is not to bash SQL Server – but it is clear that Oracle had a big headstart with their database.

  • Brian J. Parker
    February 16, 2016 4:48 pm

    I do hope this won’t turn into a generalized “SQL Server versus Oracle” beyond an objective comparison of how dates are handled; that will get quite lengthy and subjective. Both have plenty of strengths and weaknesses.

    • sorry, only meant to encourage others to not be afraid to explore Oracle, they might be really impressed. I sure was.

    • Brian J. Parker
      February 16, 2016 5:46 pm

      Oh yes, it’s definitely a great product. I’m just always afraid of flame wars. 🙂 Maybe I’m paranoid!

  • Rudimentary can’t fail…nice Clash reference!

  • I’ve had to learn a few other platforms over the past few years (Oracle NOT one of them yet) but for date math I’ve gotten used to using INTERVAL as a decent alternative to DATEADD, so instead of SELECT DATEADD(hour, -1, getdate()) you can do SELECT current_timestamp – interval ‘1 hour’.

Menu
{"cart_token":"","hash":"","cart_data":""}