Thursday, October 6, 2011

MS SQL: Day of Week

Lagt inn av Ulf kl. 5:31 PM
The day of week for a given date can in Microsoft SQL server be calculated based on the @@FirstDay system variable and the datepart function in Transact-SQL. The value returned from datepart is not constant but depends on the first day of week specified by the @@FirstDay variable. In modern programming languages like C# we will get a constant value for each day of the week. In .Net the DayOfWeek function will return 0 for sundays, 1 for mondays, etc.

A constant DayOfWeek value can be calculated in the following way:
(((@@datefirst-1) + datepart(weekday, @dtDate)) % 7)

The full Transact-SQL userdefined function is as follows:


-- =============================================
-- Description: Returns the weekday number of a given date
--
--    0 - Sunday
--    1 - Monday
--    2 - Tuesday
--    3 - Wednesday
--    4 - Thursday
--    5 - Friday
--    6 - Saturday
--                             
-- The DayOfWeek is calculated based on the current
-- @@DateFirst settings
-- between the current date and the beginning of the week
-- =============================================
CREATE FUNCTION [dbo].[sudf_Calendar_DayOfWeek]
(      
        @dtDate DateTime        -- Current date
)
RETURNS int
AS
BEGIN
        -- Variables
        declare @intDayOfWeek   int;

        -- Get the day of week
        set @intDayOfWeek = (((@@datefirst-1) + datepart(weekday, @dtDate)) % 7);

        -- Calculate the offset
        return @intDayOfWeek;
END

Source: Coragi.com



Lagt inn av Ulf kl. 5:31 PM
categories , , , , , , , , ,
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

4 kommentarer:

Jeff Moden said... at November 1, 2011 at 5:34 AM

I'd like to suggest...

SELECT (DATEDIFF(dd,'17530101',somedatetime)-1) %7

Anonymous said... at November 11, 2011 at 9:01 AM

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

Ulf said... at November 11, 2011 at 9:36 AM

You can find a description how to create a deterministic userdefined function here:
http://msdn.microsoft.com/en-us/library/aa214775(v=sql.80).aspx

You should start by schema binding the function. Use the following declaration:
create function dev_log.F_ISO_WEEK_OF_YEAR
(
@Date datetime
)
returns int WITH SCHEMABINDING

Anonymous said... at July 18, 2013 at 1:01 PM

This doesn't work
SELECT (DATEDIFF(dd,'17530101',somedatetime)-1) %7

Post a Comment

Subscribe to: Post Comments (Atom)