CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
user2329438
user2329438

Reputation: 295

SQL - Check if a date is the first occurrence of that day in its month

I have some dates that I would like to filter with SQL.

I want to be able to pass a flag to say keep all the FIRST Mondays of the months from X date to Y Date.

So essentially, I want to pass in a date and be able to tell if it's the first, second, third, fourth or last Monday (for example) of a given month.

I have already filtered down the months and days and I am currently using DATEPART(DAY, thedate) to check if the day is < 8 then 1 week < 15 2 week etc.... but this is not correct.

So the part I am stuck on is Where IsDateFirstOfMonth(@date) Where would I start to write the function IsDateFirstOfMonth? Any help much appreciated

Upvotes: 0

Views: 1995

Answers (3)

JotaBe
JotaBe

Reputation: 39004

For this kind of problem it's usually much easier to implement a table with the required date information and join on tthat table, and filter using it. I.e. create a table with this info:

CREATE TABLE Dates(
  Date DATE PRIMARY KEY CLUSTERED,
  PositionInMoth TINYINT,
  LastInMonth BIT)

Then fill up this table using whichever method you want. I think you'll do it much easyly with a simple ad-hoc app, but you can also create it using a T-SQL script.

Then you simply need to join your table with this one, and use the PositionInMoth or LastInMonth columns for filtering. You can also use this as a lookup table to easyly implement the required function.

By the way, don't forget that there are many months which have a fifth instance of a given day, for example, on december 2014 there are 5 Mondays, 5 Tuesdays, and 5 Wednesdays. The number of days with 5 instances in a given motnh is: number of days in the month - 28, for example, in December it's 31-28 = 3. So you can't count on the 4th being the last.

This table really takes up very little space, roughly, 3 bytes for the DATE, 1 byte for the TINYINT, and 1 byte for BIT, so it's 3+1+1 = 5 bytes per day, 1,825 bytes per year, and 178 kb for a whole century. So, even if you needed several centuries to cover all your possible dates, i would still be a very small table. I say roughly because the index structure, the fill factor and some other things will make the table somewhat bigger. Being such an small table means that SQL Server can easyly cache the whole table in memory when executing the queries, so your they will run really fast.

NOTE: you can expand this table to cover other needs like checking if a day is the last in the month, or the last or first working day in a month, by adding new BIT columns

Very interesting link, from OP comment: CALENDAR TABLES IN T-SQL

Upvotes: 2

user2329438
user2329438

Reputation: 295

Thank you for all you help. Hadi your reply worked perfectly but I have decide to use a tally table after all

I found a good example here http://blog.aware.co.th/calendar-tables-in-t-sql/

thanks again

Upvotes: 0

Monah
Monah

Reputation: 6784

you can do this

alter function IsDateFirstOfMonth(@date as datetime)
returns int
as
begin
    declare @first datetime,
            @last datetime,
            @temp datetime,
            @appearance int
    declare @table table(Id int identity(1,1),
                         StartDate datetime,
                         EndDate datetime,
                         DayName nvarchar(20),
                         RowNumber int)
    set @first=dateadd(day,1-day(@date),@date)
    set @last=dateadd(month,1,@first)-1
    set @temp=@first
    while @temp<=@last
    begin
        insert into @table(StartDate,EndDate,DayName) values(@temp,@temp+6,datename(dw,@temp))
        set @temp=@temp +1
    end     
    select @appearance=Nb
    from(
    select StartDate,EndDate,DayName,row_number() over (partition by DayName order by StartDate) as Nb 
    from @table) t
    where @date between t.StartDate and t.EndDate and datename(dw,@date)=t.DayName

    if @last-@date<7 
      set @appearance=-1

    return @appearance
end



select dbo.IsDateFirstOfMonth('31 Dec 2014')    
    select dbo.IsDateFirstOfMonth('03 Nov 2014') -- result 1 ( first) monday
    select dbo.IsDateFirstOfMonth('10 Nov 2014') -- result 2 (second)
    select dbo.IsDateFirstOfMonth('17 Nov 2014') -- result 3 (third)
    select dbo.IsDateFirstOfMonth('24 Nov 2014') -- result -1 (last) .... here it will be the last monday
    select dbo.IsDateFirstOfMonth('02 Nov 2014') -- result 1 ( first) sunday

hope this will help you

Upvotes: 0

Related Questions