The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.
Method 1:
Create function running following script:
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO
Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
ResultSet:
DayOfWeek
———-
Monday
Method 2: (This is update from comments I received below)
SELECT DATENAME(dw, GETDATE())
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
nice blog thanks
SELECT DATENAME(weekday, ‘1988/08/31’) will do
It doesn’t have to be anywhere as near as complicated as that try this:
TO_CHAR(column_name, ‘Day dd-mon-yy’)
you can include this in other scripts.
I used this in a report i run daily to let me know when invoices need to be authorised. This script excludes weekends in a CASE:
TO_CHAR (( CASE
WHEN TO_CHAR(ps.run_date,’DY’) = ‘MON’
THEN TRUNC(ps.run_date)-3
ELSE TRUNC(ps.run_date)-1
END), ‘Day dd-mon-yy’) AS AUTHORISE_BY
Oh yes, I’m English, so i spell authorised in an English way.
I want to those record for the start date are from Monday to Sunday in sql query
Thanks
Hi,
I want to create one attendance report so,in that i want two columns one is “Total Working days” of the week and second column is “number of days present”. please help me for the same.
Pinal,
Thank you for your blogposts. They have helped me a lot when I am stuck in SQL.
Please keep up the good work.
Hi Pinal,
i need your help on the below reuirement.
i have a table called attendance having column EmpId,Department,date,status
i want to have a weekly headcount report where i can get the data in the desied format like
Department | W1 |W2|W3|W4|W5|W6|W7|W8 and so on…
_____________________________________________
Dep1 20 23 25 26 27 21 and so on
Dep2 76 77 63 76 72 27 and so on
Dep3 75 52 46 57 68 53 76 and so on..
W1,W2 is nothing but week number
Actully report is to get emp head count weekwise..
Can u suggest me somthing to sortout this issue..