Wednesday, 21 August 2024

Get the first & last Sunday of every month

 Below script is used to get the list of first and last Sunday of each month.

DECLARE @Year INT

SET @Year =2024

SELECT months,

       Min(dates) AS Firts_Sunday_OF_The_Month,

       Max(dates) AS Last_Sunday_OF_The_Month

FROM   (SELECT Month(Dateadd(day, number - 1, Dateadd(year, @Year - 1900, 0)))

               AS

                      Months,

               Dateadd(day, number - 1, Dateadd(year, @Year - 1900, 0))

               AS Dates

        FROM   master..spt_values

        WHERE  type = 'P'

 AND number BETWEEN 1 AND Datediff(day, Dateadd(year, @Year - 1900

                                                      , 0),

                                            Dateadd(year, @Year - 1900 +

                                                          1, 0)

                                        )) AS T

WHERE  Datename(weekday, dates) = 'Sunday'

GROUP  BY months,

          Dateadd(month, Datediff(month, 0, dates), 0)




No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts