Thursday, October 6, 2011

MS SQL: Day of Week

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 kl.
Etiketter: , , , , , , , , ,

4 comments:

  1. Jeff ModenNovember 1, 2011 at 5:34 AM

    I'd like to suggest...

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

    ReplyDelete
  2. AnonymousNovember 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

    ReplyDelete
  3. UlfNovember 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

    ReplyDelete
  4. AnonymousJuly 18, 2013 at 1:01 PM

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

    ReplyDelete

Subscribe to: Post Comments (Atom)