0

I have seen and used various forms of DateAdd and DateDiff to strip time, get to the first day of any month or to move around a date.

I can see that when performing these calculation a pattern often used is as below:

select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) as 'Beginning of the month'

I know that 0 inside the DateDiff expression is "1 Jan 1900". It first gets the date difference in MONTHS between the current month and "1 jan 1900". Then adds back the number of months to "1 Jan 1900" and that way it looses the day information and defaults to the 1 of the month.

I have googled and also tried myself using the technique above to find out a way with which I can may be find out the date of when it is the "First Friday of April". The below snippet is not correct but I was hoping there could be something like the below to find the financial week and year:

DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)

What I eventually need to do is find out all the financial week numbers (and the corresponding financial year) for any given date range.

The first financial week starts from "1st Friday of April". The second week starts from the next Friday and so on.

I understand that a DATE table would serve and be a lot more productive but I would really like to find a way to do this way for now.

I have written a table-valued function that will produce the data set of a calendar but I need to add the below two columns from the logic I have stated above to make it complete:

  1. FinancialWeekNumber
  2. FinancialYear

Below is the code I have written so far:

create function [dbo].[MyDailyDateTable]
      (@StartDate datetime, @EndDate datetime)          returns @DailyDates table (
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int

    ---- FinancialWeekNumber
    ---- FinancialYear ) AS BEGIN

    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select  @StartDate DailyDate
             , datename(day, @StartDate) DayNumber 
                , datename(dw, @StartDate) DayName 
             , DATEPART(dw, @StartDate) DayOfTheWeek -- Default 1 = Monday is used ("SET DATEFIRST 1")
                , datepart(week, @StartDate) WeekNumber
             , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
                , datename(month,@StartDate) MonthCalled
                , YEAR(@StartDate) YearNumber

             ---- FinancialWeekNumber
             ---- FinancialYear

        set @StartDate = dateadd(day, 1 ,@StartDate)
    end

    return END
Unbound
  • 177
  • 2
  • 14
  • 1
    Well if you do a datediff on a given date since 1st April that will give you the number of days. just do a divide on the number of days by 7 rounded down as an integer, then add 1. What else is there? Am I missing something? (Get the year part for date you are processing, create a date for 1st April of that year - and do datediff on those two) – JGFMK Feb 23 '19 at 07:50
  • it's the first Friday of April which will be at different days in April in different years. – Unbound Feb 23 '19 at 21:57
  • http://mathforum.org/dr.math/faq/faq.calendar.html – JGFMK Feb 24 '19 at 14:47

2 Answers2

0

This will give you the date of the first Friday in April as a function of 'theyear'. Essentially it adds the difference between the Friday weekday number (6) and the weekday number of 1st April to the date of 1st April of the given year. (This assumes Sunday=1). The week number can then be derived from the calculated date for that week or any other week.

declare @theyear int
set @theyear = datepart(yy,getdate())

select dateadd(day, 
    case when (6 - datepart(dw, datefromparts(@theyear, 4, 1))) < 0 then 6 
    else (6 - datepart(dw, datefromparts(@theyear, 4, 1))) end, 
    datefromparts(@theyear, 4, 1))
J.R.
  • 1,880
  • 8
  • 16
  • You are right; I have edited the answer to handle the situation when the weekday number becomes negative. – J.R. Feb 25 '19 at 10:32
0

I could not exactly resolve this the way I wanted but I needed to get this done so I have written some other script to help me with this. I have posted it here.

Unbound
  • 177
  • 2
  • 14