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.
Create function running following script:
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
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'
Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
Method 2: (This is update from comments I received below)
SELECT DATENAME(dw, GETDATE())
Reference : Pinal Dave (https://blog.sqlauthority.com)
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
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
declare @WeekOfYear int
— 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.
— Case finds start of year
when NextYrStart <= @date
when CurrYrStart <= @date
— First day of first week of prior year
— First day of first week of current year
— First day of first week of next year
–Find Jan 4 for the year of the input date
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’
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
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.
Thank you for your blogposts. They have helped me a lot when I am stuck in SQL.
Please keep up the good work.
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..