The Leading Educational Resource for IT Professionals

SQL 101: Date-Related Functions, Part 3 - Extracting Information from Dates

by Victoria Mack August 19, 2016 0 Comments

This article continues the date-related functions discussion, introducing a few more simple but extremely useful SQL functions: DAYOFWEEK, WEEK, QUARTER, DAYOFYEAR, and MIDNIGHT_SECONDS. Do you have time for some date fun?


Let me start with a quick flashback: an RPG Academy TechTip published in October 2015, explaining how to create an RPG function to calculate the day of the week of a given date stirred things up quite a bit. Some readers complained this kind of function was totally unnecessary, because SQL is better equipped to do this type of thing and so on. My reply was that I’d get to a point in the SQL 101 series in which I’d cover the “SQL version” of that particular function, named Clc_DayOfWeek.


Fast forward to today: SQL provides not one, but two functions (which do the exact same thing, as far as I know) that replace the aforementioned RPG function with a single statement. Functions DAYOFWEEK and DAYOFWEEK_ISO both accept a valid date representation in a date, timestamp, or string data type, and return an integer between 1 (meaning Sunday) and 7 (Saturday). As shown in another RPG Academy TechTip, the one that presented the Clc_HowLongUntilWE function, this can be useful to calculate the number of business days between two dates. Here’s an example:


SELECT      DAYOFWEEK(‘2015-03-04’)



The execution of this statement will return 4, the numeric representation of Wednesday.


There are also two functions to retrieve the week number: WEEK and WEEK_ISO. Unlike the two day-related functions just presented, these work differently from each other. While WEEK considers that the week starts with Sunday and that January 1 is always in the first week, WEEK_ISO considers that the week starts with Monday and that week 1 is the first week of the year containing a Thursday, which is equivalent to the first week containing January 4. Thus, it is possible to have up to three days at the beginning of the year returned by WEEK_ISO as the last week of the previous year, or to have up to three days at the end of a year appear as the first week of the next year.


The input parameter is the same for both functions: the usual valid date representation in a date, timestamp, or string data type. The WEEK function returns an integer between 1 and 54, while WEEK_ISO returns the same data type from 1 to 53. Here’s an example, combining the two functions:


SELECT      WEEK(‘2015-01-01’) “WEEK”

            , WEEK_ISO(‘2015-01-01’) “WEEK_ISO”



This will return two columns with the headings WEEK and WEEK_ISO, and a single line with two 1s. Why? Well, WEEK considers week 1 the week that contains January 1, while WEEK_ISO does the same for the first Thursday of the year. In 2015, January 1 was a Thursday, so both functions return the same thing. If I change the date to January 1, 1999, however, the results are different:


SELECT      WEEK(‘1999-01-01’) “WEEK”

            , WEEK_ISO(‘1999-01-01’) “WEEK_ISO”



Here, the WEEK function still returns 1, but WEEK_ISO now returns 53, because it considers that January 1, 1999 is still part of the last week of 1998. Now that you’re aware of the possible discrepancies, choose between these functions carefully.


Determining the Quarter

If you need the quarter instead of the week, SQL’s QUARTER function is the answer. It shares the single parameter model of the previously presented functions and returns an integer between 1 and 4, representing the quarter in which the date represented in the input parameter resides. For example, any date in April, May, or June, regardless of the year, will cause the function to return a 2 (the second quarter). Here’s an example:


SELECT      QUARTER(‘2015-04-24’)



This statement returns 2, because April is part of the second quarter.


I’m almost done with the date-extraction SQL functions. There are just two to go. The first is the DAYOFYEAR function, which has the customary input parameter and returns an integer representing how many days have elapsed since January 1 of the year represented in the input parameter. For instance, the following statement returns 34, because 34 days elapsed between January 1, 2015 and February 2, 2015:


SELECT      DAYOFYEAR(‘2015-02-02’)



Similarly, the MIDNIGHT_SECONDS function returns the number of seconds that have passed since 00.00.00 of the date indicated in the input parameter. Note that a valid date representation, such as ‘2015-02-03’, implicitly contains 00.00.00 as its time, which will cause the function to return 0. With an input parameter that actually represents time, such as a time or timestamp, the output will be somewhere between 0 and 86.400. A couple of examples will make things clearer:


SELECT      MIDNIGHT_SECONDS(‘2015-02-03-’)

            , MIDNIGHT_SECONDS(‘2015-02-03-’)



This returns 60 and 44.125. The first example should be simple to calculate; after all, only a minute (or 60 seconds) elapsed since midnight. The second example is a bit trickier, but the math is simple enough: (3600 * 12) + (60 * 15) + 25 = 44.125.


The next article will cover some more date math, discussing something that seems simple, but it’s not: adding and subtracting months to dates. Until then, I’d like you to share your real-world experience with these functions: how and why did you use them in a production environment? Use the comments section below to speak your mind!


About the author: Rafael Victoria-Pereira

Victoria Mack
Victoria Mack


Also in MC Press Articles

Identity Management Provisioning and Workflow – A core competence

by Graham Williamson April 13, 2017 0 Comments

Identity provisioning, with an approval workflow, is a core competence for CIOs yet many struggle with a confusing array of tasks that form the provisioning process within their organisations.

Continue Reading →

Identity Management - Why Is the Level of Interest So Low?

by Graham Williamson April 11, 2017 0 Comments

It's critical that CIOs and C-levels pay attention to identity and access management in their organizations. Doing so will boost business security and save the business money, too.

Continue Reading →

Gifts to Give Yourself This Holiday

by Victoria Mack December 21, 2016 0 Comments

It’s the holiday season, and everyone is in the gift-giving mood. Be sure you don’t forget to invest in your company and career.

brian mayWritten by Brian May

It’s a special time of the year. Family gatherings for the holidays, football season, and time in the woods all make this one of my favorite seasons. The month of December is also unique for IT departments. December is certainly not business as usual for most of us.

It’s time for budgets. That may mean requesting budget items for next year or spending surplus budget before the end of the year. It’s often when project work slows down a bit as end users, and IT staff alike, take time away from the office. It’s a time when stress is often at its lowest, and it’s just easier to get some things done.

Continue Reading →