Please–stop substringing dates. There are much easier, more efficient, and more robust ways of getting what you want.

Date Literals

We’ll start with an ANSI-style hard coded date.

Similarly, here are variations of ANSI-style timestamps.

If you select dates without doing anything fancy like converting their format with a TO_CHAR() function, the dates will display, and be interpreted/coerced by default in the format specified by the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

In other words, this query won’t fly (by default) in Oracle:

But!  But!  But!
If we alter the NLS_DATE_FORMAT session variable, it will work.

Now that you’ve altered the NLS_DATE_FORMAT, Oracle automatically coerces the string ’07/01/1981′ into a date.  Notice, also, that the format that dates are displayed in are different after altering the NLS_DATE_FORMAT.

If your objective is not to measure a point in time, but rather a length of time, you can use an interval data type.  There are two different flavors of interval– YEAR TO MONTH intervals (which measure time in years and months) and DAY TO SECOND intervals (which measure time in days, hours, minutes, and seconds).  Examples of those are below:

Date Math Operations

You can add intervals to dates.

You can also add intervals to intervals, as long as they are the same type.

You cannot, however, add a YEAR TO MONTH interval to a DAY TO SECOND interval (think about it…what kind of datatype would it store the result in?)

You can add/subtract days to a date by adding/subtracting  an integer

You can’t add two dates together (not sure what the answer would be) but you can subtract two dates to get the number of days between them.

You can also do the same thing with timestamps; but instead of returning the number of days between the two timestamps, it returns a DAY TO SECOND INTERVAL type.

 

Obtaining the Current Date/Time

There are 5 different ways to get a date or timestamp that represents “right now.”

SYSDATE – Returns the current date of the server.  So if you’re in New York, and you connect to a server in San Francisco, running SYSDATE will give you the current time in the Pacific timezone.


SYSTIMESTAMP
– Same as SYSDATE, but returns a timestamp instead of a date.


CURRENT_DATE
– Returns the current time in the client’s timezone.  So if you’re in New York, and you connect to a server in San Francisco, running CURRENT_DATE will give you the current time in the Eastern timezone.  When you connect to the database, you can set what timezone you’re connecting from with the ALTER SESSION SET TIME_ZONE command;


CURRENT_TIMESTAMP
– Similar to CURRENT_DATE, and the return type is TIMESTAMP WITH TIME ZONE data type.


LOCALTIMESTAMP
– Similar to CURRENT_TIMESTAMP, except it returns a TIMESTAMP WITHOUT TIME ZONE data type.

 

Date Functions

ADD_MONTHS() – Takes two arguments, a date and an integer.  Returns the date, plus <integer> months.  If the parameter date is the last day of the month…or, if the resulting month has fewer days…then the result will fall on the last day of the month.


DBTIMEZONE()
– Returns the database’s time zone, as set by CREATE DATABASE or ALTER DATABASE SET TIME_ZONE statement.


EXTRACT()
– Returns a portion of a date, timestamp, or interval data type.  You use it like this:

Things you can extract are:

  • YEAR
  • MONTH
  • DATE
  • HOUR*
  • MINUTE*
  • SECOND*
  • TIMEZONE_HOUR**
  • TIMEZONE_MINUTE**
  • TIMEZONE_REGION**
  • TIMEZONE_ABBR**

* Even though in Oracle, a date includes an hour, minute, and second portion, you cannot extract an hour, minute, or second from a date.  You can, however, cast the date to a timestamp, and subsequently extract those pieces.

** Timezone pieces can only be extracted from data types that include timezones (e.g. TIMESTAMP WITH TIMEZONE)


FROM_TZ()
– converts a TIMESTAMP data type to a TIMESTAMP WITH TIMEZONE data type.


LAST_DAY()
– Takes a date argument and returns the last day of the date’s month.


MONTHS_BETWEEN()
– Takes two date parameters and returns a (potentially negative) decimal number that indicates the number of dates between the two supplied dates.


NEW_TIME()
– Converts a date from a time in one time zone, to a time in another time zone.


NEXT_DAY()
– Accepts a date parameter and a text string parameter indicating a day of the week and returns the date of the next day of the week.  This function will never return the date that’s passed to it.  In other words if November 13th, 2015 is a Friday, and you run the following:

…it will return Nov 20th, 2015.  In other words, the Friday following the specified date.


NUMTODSINTERVAL()
– Takes two arguments, a number and a character string representing a unit of time.  Returns an INTERVAL DAY TO SECOND data type.  Valid units are DAY, HOUR, MINUTE, and SECOND (case doesn’t matter).


NUMTOYMINTERVAL()
– Takes two arguments, a number and a character string representing a unit of time.  Returns an INTERVAL YEAR TO MONTH data type.  Valid units are YEAR and MONTH (case doesn’t matter).


ROUND()
– You can provide a date  and a format string and round a date to the nearest specified point in time.  If no format string is provided, the date is rounded to the nearest day


SESSIONTIMEZONE()
– Returns the database’s timezone per the last ALTER SESSION statement.


SYS_EXTRACT_UTC()
– Accepts a single argument (must be a TIMESTAMP WITH TIMEZONE data type) and returns the UTC (Greenwich Mean Time) time for the timestamp.


TO_DSINTERVAL()
– Converts a character string to a DAY TO SECOND INTERVAL data type.


TO_YMINTERVAL()
– Converts a character string to a YEAR TO MONTH INTERVAL data type.


TRUNC()
– Truncates a date to the nearest specified point in time, as determined by a format string.  If no format string is supplied, truncates the supplied date to the nearest day.


TZ_OFFSET()
– Accepts a single argument–a text string indicating the name of a timezone.  Returns the numeric time offset for the timezone.

Date Formatting

You’re likely already familiar with Oracle’s TO_DATE() and TO_CHAR() functions to convert from a string to a date, and vice-versa.  Below are some formatting codes:

Formatting Code

Format-Code Description

AD or BC Epoch indicator
A.D. or B.C. Epoch indicator with periods
AM or PM Meridian indicator
A.M. or P.M. Meridian indicator with periods
DY Day of week, abbreviated
DAY Day of week, spelled out
D Day of week (1-7)
DD Day of month (1-31)
DDD Day of year (1-366)
DL Long date format
DS Short date format
TS Time in short format
FF Fractional seconds
 J Julian Days (days since 4,712 BC)
 W Week of the month (1-5)
 WW, IW Week of the year, ISO week of the year
MM Two digit month
MON Month name, abbreviated
MONTH Month name, spelled out
 Q Quarter
RM Roman numeral month (I through XII)
 YYYY, YYY, YY, Y Four-digit year; last 3, 2, 1 digits of year
 YEAR Year, spelled out
SYYYY If BC, year is shown as negative
RR, RRRR Year, used for data input (allows two-digit years to be input)
 CC, SCC Century
HH, HH12 Hour of the day (1-12)
HH24 Hour of the day (0-23)
 MI Minute of the hour (0-59)
SS Seconds (0-59)
SSSSS Seconds of the day (0-86399)
 TZD Time zone, daylight savings
TZH Time zone hour (when added to TZM forms time zone offset)
TZM Time zone minute (when added to TZH forms time zone offset)
TZR Time Zone Region
“text” quoted text
FM Fill mode (no leading or trailing spaces)
FX Force exact match for date format
SP Spell out the number
TH Add cardinality suffix (1 becomes 1st, 2 becomes 2nd, 3 becomes 3rd…)

Examples

To calculate what day Labor Day will fall on, you could run something like this:

You can calculate when Thanksgiving will happen with something like this:

You can get a “plain English” date with some fancy TO_CHAR() footwork.

You can convert a number to it’s spelled out version by converting it to a date, and then into a character.

Now go forth, and substring no more.