locked
How can I get sql to get the dates of first Saturday in each month between two dates RRS feed

  • Question

  • Declare startdate datetime, enddate datetime

    set startdate='01-01-2019'

    set enddate = '12-31-2019'

    What is the sql to get  all the date  of  first Saturday   in each month between two dates, Please can you help and would be much appreciated

    Regards

    Pol


    polachan

    Tuesday, May 14, 2019 2:27 PM

Answers

  • DECLARE @startdate date = '01-01-2019';
    DECLARE @enddate date = '12-31-2019';
    DECLARE @processDate date;
    DECLARE @FirstSaturdayPerMonth TABLE (
    	[FirstSaturdayDate] date
    );
    
    WHILE @startdate < @enddate
    BEGIN
    	SET @processDate = @startdate
    	WHILE 1 = 1
    	BEGIN
    		IF DATENAME(weekday, @processDate) = 'Saturday'
    		BEGIN
    			INSERT INTO @FirstSaturdayPerMonth([FirstSaturdayDate]) VALUES(@processDate);
    			--PRINT @processDate;
    			BREAK;
    		END
    		SET @processDate = DATEADD(day, 1, @processDate);
    	END
    
    	SET @startdate = DATEADD(month, 1, @startdate);
    END
    
    SELECT [FirstSaturdayDate] FROM  @FirstSaturdayPerMonth;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by polachan Tuesday, May 14, 2019 9:21 PM
    Tuesday, May 14, 2019 4:24 PM

All replies

  • Declare @startdate datetime, @enddate datetime,@firstSaturday datetime
    
    set @startdate='01-01-2019'
    set @enddate = '12-31-2019'
    
    
    SET @firstSaturday = dateadd(day,7 - DATEPART(weekday,@startdate),@startdate)
    IF @firstSaturday > @enddate
        SET @firstSaturday = NULL;
    
    SELECT @firstSaturday

    Tuesday, May 14, 2019 2:56 PM
  • DECLARE @startdate date = '01-01-2019';
    DECLARE @enddate date = '12-31-2019';
    DECLARE @processDate date;
    DECLARE @FirstSaturdayPerMonth TABLE (
    	[FirstSaturdayDate] date
    );
    
    WHILE @startdate < @enddate
    BEGIN
    	SET @processDate = @startdate
    	WHILE 1 = 1
    	BEGIN
    		IF DATENAME(weekday, @processDate) = 'Saturday'
    		BEGIN
    			INSERT INTO @FirstSaturdayPerMonth([FirstSaturdayDate]) VALUES(@processDate);
    			--PRINT @processDate;
    			BREAK;
    		END
    		SET @processDate = DATEADD(day, 1, @processDate);
    	END
    
    	SET @startdate = DATEADD(month, 1, @startdate);
    END
    
    SELECT [FirstSaturdayDate] FROM  @FirstSaturdayPerMonth;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by polachan Tuesday, May 14, 2019 9:21 PM
    Tuesday, May 14, 2019 4:24 PM
  • Using a calendar table will make it easy to accomplish.

    with R as (
    select [date], row_number(partition by year([date]), month([date]) order by [date]) as rn
    from dbo.calendar
    where dofw = 'Saturday' and [date] between @sdt and @edt
    )
    select [date]
    from R
    where rn = 1;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Tuesday, May 14, 2019 6:53 PM
  • Please learn that SQL uses the ISO-8601 "yyyy-mm-dd" display format, not your local dialect. Since SQL is a declarative language, I would use my calendar table for this.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, May 14, 2019 10:05 PM