SQL Standard

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:

SELECT DAY(TIMESTAMP('2012-04-01 10:07:05')) AS day_of_week;

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:

EXTRACT(part FROM date_expression)

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

  2. DAY
  4. WEEK
  5. WEEK
  8. MONTH
  10. YEAR

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:

  EXTRACT(DAYOFWEEK FROM DATE '2022-04-01') AS day_of_week;

The above query should return the output as:



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

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list