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
why wouldn’t you use the DATENAME function?
Why not use the datename() function instead – it’s already built in to SQL Server?
IE – SELECT DATENAME(dw, GETDATE())
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 0 THEN ‘Sunday’
WHEN 1 THEN ‘Monday’
WHEN 2 THEN ‘Tuesday’
WHEN 3 THEN ‘Wednesday’
WHEN 4 THEN ‘Thursday’
WHEN 5 THEN ‘Friday’
WHEN 6 THEN ‘Saturday’
END
RETURN (@rtDayofWeek)
END
GO
for right result we have to change this to
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
Why would you even keep the first method up on the site when there is a built in function that performs the same job? It doesn’t seem like a best practice to rewrite working functions on your SQL Server.
The tricky thing here is that Sunday does not always equal 1. The values returned by DATEPART(dw) will change based on the setting of @@DATEFIRST (which indicates the first day of the week). You will notice this problem most readily in locales where Monday (or some other day) is the first day of the week. To test, invoke “SET DATEFIRST 1” and try the provided function.
To get past this issue, you can try the following:
(DATEPART(dw, @SomeDate) + @@DATEFIRST) % 7
When you do this, you’ll see that a Wednesday is 4, regardless of the DATEFIRST setting.
Note that in this code the day is from 0 to 6, not 1 to 7.
i.e. saturday is 0.
1.how to get only system TIME using query
2.how to get only system DATE using query
select getdate()
Hi! Pinal,
I need to know, How can I display Date like
Mon, 20th Dec 2007
dbo.udf_PreviousThursday
=====================
Here is a useful UDF which takes a datetime value as input and returns as datetime output the previous Thursday:
ALTER FUNCTION dbo.udf_PreviousThursday(@dtDate DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @dt_PreviousThursday DATETIME
DECLARE @i_SubtractBy int –the number of days you must subtract from the input date to get the previous Thursday
SELECT @i_SubtractBy =
CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN -4 –(i.e when Monday subtract 4 days to get the previous Thursday)
WHEN 2 THEN -5 –(i.e when Tuesday subtract 5 days to get the previous Thursday)
WHEN 3 THEN -6 –(i.e when Wednesday subtract 6 days to get the previous Thursday)
WHEN 4 THEN -7 –(i.e when Thursday subtract 7 days to get the previous Thursday) should this be zero??
WHEN 5 THEN -1 –etc.
WHEN 6 THEN -2 –etc.
WHEN 7 THEN -3 –etc.
END
SELECT @dt_PreviousThursday = DATEADD(day, @i_SubtractBy, @dtDate)
RETURN @dt_PreviousThursday
END
GO
–Sample execute
SET DATEFIRST 1–set Monday i.e. not Sunday to be the first day of the week
SELECT dbo.udf_PreviousThursday(getdate())
SELECT dbo.udf_PreviousThursday(‘2007-01-12 13:52:09.043’)
In reply to Smita’s question, one of the existing datetime conversion formats can normally be relied upon for the conversion of datetime to string
e.g.
SELECT CONVERT(VARCHAR(100), GETDATE(), 106)
A list of common conversion styles is here:
https://www.microsoft.com/en-us/download/details.aspx?id=51958
Of course, if you want to do something quite specific, you will have to use DATEPARTs to extract the relevant parts of the date, and concatenate these together. You need to convert all the parts to varchar prior to concatenation or you’ll get an error.
As a useful tip also, if you are getting 1/12/2007 and you want to get 01/12/2007 you need to use the right function like so:
right( ’00’ + Cast(datepart(dd, getdate()) as varchar), 2)
Hi pinal,
I need to know, How can I count the working days excluding sunday and saturday.
like if i need to count 7 working days..and today is monday so my seventh working days should be tuesday and i have to display the tuesday instead of the date fallin on tuesday.
Any help will be appreciated .
Regards
Vivek
@vivek
Give me some more details, like are you trying to retrieve data from a table, if yes then give the table structure what are the columns … that will be help me a little bit.
Thanks.
This function will let you correct day of the week considering the fact that DateFirst need not be always equal to 1
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
DECLARE @weekDay int
— Here I have subtracted 7 For keeping Sunday as the First day
— like wise for Monday we need to subtract 2 and so on
@weekDay = Select ((DatePart(dw,GETDATE())+@@DATEFIRST-7)%7)
SELECT @rtDayofWeek = CASE @weekDay
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
Everything shows as Monday, regardless of the date. UGH
Hi, I want to get the dates of working days in a week
Can anybody help me.
Thanks in advance,
Partha
Dear Sir,
I need your help. I want to get the starting date and ending date for input week number. I am using sql server 2005.
Please do reply me soon.
Byyee and have a nice day.
Sunil,
Good question, I need the same solution.
Chris
Hi,
Please find this code..it might helps you.
–it calculates based upon current years Week#
DECLARE @week INT,
@weekdt DATETIME
SELECT @week = 40 –For Current Week
–get First day of current year
SELECT @weekdt = DATEADD(wk, @week, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
SELECT @week AS Week#,
DATEADD(DD, 1 – DATEPART(DW, @weekdt), @weekdt) As WeekStartDate,
DATEADD(DD, 6, DATEADD(DD, 1 – DATEPART(DW, @weekdt), @weekdt)) As WeekEndDate
Can i get spanish names whit DATENAME ?
Yes Only if the default language of the server is Spanish
my question is i putup a any date of future and want to make calendre according to this way of any flight
my table is like
flightno sun mon tues wed thurs friday saturday
1001 y y n n n y n
1002 n y n y y n y
Sebastian,
This may help you
;WITH CTE AS
(
SELECT
1 AS ID,
SUBSTRING(days,0,CHARINDEX(‘,’,days)) AS Days,
SUBSTRING(days,CHARINDEX(‘,’,days)+1,LEN(days))RemainingStr,
2%8 AS DayID
FROM
sys.sysLanguages
WHERE
alias = ‘spanish’
UNION ALL
SELECT
ID+1 AS ID ,
CASE WHEN CHARINDEX(‘,’,RemainingStr) > 0 THEN SUBSTRING(RemainingStr,0,CHARINDEX(‘,’,RemainingStr))ELSE RemainingStr END AS Days,
SUBSTRING(RemainingStr,CHARINDEX(‘,’,RemainingStr)+1,LEN(RemainingStr))RemainingStr,
(DayID+1)%8 AS DayID
FROM
CTE
WHERE
ID < 7
)
SELECT Days,DAyID FROM CTE
HI, great post…
But, it’s possible with only data result “15-07-2007” (called date) you know what is the first day of the week?
For example I want this result:
datepart(month, date) + ‘ ‘ + datepart(week, date) + ‘ ‘ + “First day of the week”
How I catch “First day of the week” ???
thanks in advance
Probably you need
select dateadd(week,datediff(week,0,date),0)