SQL Day of Week

Date and time are significant when working with data. They allow us to keep track of the changes made to the database within specific time intervals.

However, in SQL databases, you need to specify the date in full (i.e., the year, month, and date.) Hence, if you need to access only a specific part of the date, you must perform a particular operation.

This article will look at how we can extract the day of the week from a date value using both Legacy and Standard SQL.

Although some database engines still support legacy SQL, we recommend the option for Standard SQL to ensure compatibility with major database engines.

Legacy SQL

The first method we will discuss is extracting the day of the week using Legacy SQL. For this, we will use the dayofweek() function.

This function takes a timestamp data type as the argument and returns the day of the week. The return value is an integer type ranging from 1 to 7: where 1 is Sunday, and seven is Saturday inclusive.

The function syntax is as shown below:

This function illustrated in the example below:

The above query should return an integer indicating the day of the week. Example output is as shown:

Standard SQL.

Unlike Legacy SQL, Standard SQL provides us with one function to extract various parts of datetime.

The extract() function extracts a specific part from a timestamp type. The function syntax is as shown:

The function takes the part to extract the timestamp. The part can include the following options:

1. DAYOFWEEK
2. DAY
3. DAYOFYEAR
4. WEEK
5. WEEK
6. WEEKDAY
7. ISOWEEK
8. MONTH
9. QUARTER
10. YEAR
11. ISOYEAR

For this tutorial, we are interested in extracting the day of the week so that, we can specify the part as DAYOFWEEK.

The function should return an integer value ranging from 1 to 7.

An example is as shown:

The above query should return the output as:

Conclusion

This article discussed two main methods of extracting the day of the week from a date object in Legacy and Standard SQL.