Weekday function

In SQL Server, there is no direct WEEKDAY() function like in some other database systems. However, you can use the DATENAME or DATEPART function along with DATEADD or GETDATE to extract the weekday or day of the week from a date.

Here are two common ways to achieve this:

1. Using DATEPART():

DATEPART returns the integer representing the part of the date you specify. For weekdays, SQL Server considers Sunday as the first day of the week by default, and it returns 1 for Sunday, 2 for Monday, and so on.

SELECT DATEPART(WEEKDAY, GETDATE()) AS WeekdayNumber;

This will return an integer value between 1 and 7, representing the current day of the week.

Customizing the Start of the Week:

If you’d like to start the week on a different day (e.g., Monday), you can use SET DATEFIRST to change it. For instance:

SET DATEFIRST 1; -- Sets Monday as the first day of the week
SELECT DATEPART(WEEKDAY, GETDATE()) AS WeekdayNumber;

2. Using DATENAME():

DATENAME() returns the name of the part of the date you specify (e.g., Sunday, Monday, etc.).

SELECT DATENAME(WEEKDAY, GETDATE()) AS WeekdayName;

This will return the name of the current weekday (e.g., ‘Monday’).

Formatting Weekdays in Queries:

You can use these functions in your queries to group by, filter, or display specific weekday-related data. Here’s an example query filtering rows based on a specific weekday:

SELECT *
FROM YourTable
WHERE DATEPART(WEEKDAY, YourDateColumn) = 2; -- Filters rows where the date falls on a Monday

For more complex scenarios, you can combine these functions with CASE statements to handle custom weekday logic.