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())
Watch SET DATEFIRST When You Get the Day of the Week
Here is one thing to watch with Method 1. The number DATEPART returns for the day of the week is not fixed. It depends on the DATEFIRST setting of the session. With the default us_english language, Sunday is 1 and Saturday is 7, which is exactly what the CASE statement in the function expects. If a login uses a different language, or someone runs SET DATEFIRST 1, Monday becomes 1 and the function quietly returns the wrong name.
SELECT @@DATEFIRST AS DateFirstSetting;
SET DATEFIRST 1;
SELECT DATEPART(weekday, GETDATE()) AS DayNumber,
DATENAME(weekday, GETDATE()) AS DayName;Run this small test and compare the day number with your earlier result. DATENAME in Method 2 does not care about DATEFIRST at all, so it is the safer choice when you only need the name. It returns the name in the language of the session, so an English login gets English names. If you still want the number, check @@DATEFIRST first and do not assume Sunday is always 1. It is a tiny check that saves a very confusing bug later.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
56 Comments. Leave new
Hi I have created computed col with used following function for GET WEEK OF THE DATE but when create a index on that it says this is Non deterministic function, please help me on that how can i convert it into deterministic
create function dev_log.F_ISO_WEEK_OF_YEAR
(
@Date datetime
)
returns int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin
declare @WeekOfYear int
select
— Compute week of year as (days since start of year/7)+1
— Division by 7 gives whole weeks since start of year.
— Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
— Case finds start of year
case
when NextYrStart <= @date
then NextYrStart
when CurrYrStart <= @date
then CurrYrStart
else PriorYrStart
end,@date)/7)+1
from
(
select
— First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
— First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
— First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
–Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a
return @WeekOfYear
end
go
i want to display week days dates of the month from created date .
E: if i select radio button as week days on 11/11/2011
it should calculate nov months week dates
if i select calender from to to date it should select only week end dates
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..