I am going to endure the journey of writing more short articles or tidbits on Transaction SQL date/time functions.
Most, if not all, modern day computer systems collect data which contains dates and times.
Not only is it important to know when a sales order came in, but we should fulfill the request using some type of queue (LIFO). If if was a stack (FIFO) implementation, the first person may never get his order!
Today, I will be exploring the DATEPART() function. This function has 15 different input parameters that can be passed to change the resulting output.
In short, the function takes a date part and date time variables as input returns the part that you want as an integer. I will be concentrating on how to extract the day of the week (DW, W) in this article. One thing that influences the return value is what day is the start of the week?
The examples below are sample calls to the function. The inputs puts are valid date, null value and a empty string.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Functions to get date and time parts as INT -- -- Show setting (1=mon ... 7=sun) SELECT @@DATEFIRST AS SETTING GO -- Week day SELECT DATEPART(DW, SYSDATETIME()) AS WEEKDAY_A_INT, DATEPART(W, SYSDATETIME()) AS WEEKDAY_B_INT, DATEPART(DW, NULL) AS WEEKDAY_C_INT, DATEPART(W, '') AS WEEKDAY_D_INT GO |
The output from the test is listed below. It is surprising that an empty string returns a value of 2 or Monday.
1 2 3 4 5 6 7 8 9 |
output: SETTING ------- 7 WEEKDAY_A_INT WEEKDAY_B_INT WEEKDAY_C_INT WEEKDAY_D_INT ------------- ------------- ------------- ------------- 4 4 NULL 2 |
In as nutshell, pass one of the two date part parameters to extract the day of the week as an integer from the date time variable.