To get All dates, Months, quarters and year with in two dates

Use the query below to get all the dates, months, quarters and years with in two dates.


if object_id('tempdb..#Calendar_Test')is not null
drop table #Calendar_Test

declare @Date_Start datetime, @Date_End datetime

select @Date_Start = '2000-01-01 00:00:00.000'
select @Date_End = '2020-12-31 00:00:00.000';
with
CTE_Dates as (
    select @Date_Start as [Date]
    union all
    select dateadd(dd, 1, [Date])
    from CTE_Dates
    where [Date] < @Date_End
),
CTE_Calendar as (
    select
        [Date],
datename(dd, [Date]) as [Day_ID],
        datename(dw, [Date]) as [Day],
        datepart(ww, [Date]) as [Week],
        datepart(mm, [Date]) as [MonthID],
        datepart(yy, [Date]) as [YearID],
        datepart(qq, [Date]) as [QuarterID]
     from CTE_Dates
)



select   *
into #Calendar_Test
from CTE_Calendar
option (maxrecursion 0)

Comments

  1. Muhammad Jawad3 May 2016 at 02:33


    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME

    SET @StartDate='01/01/2010'
    SET @EndDate = GETDATE()

    DECLARE @LoopDate DATETIME
    SET @LoopDate=@StartDate


    CREATE TABLE Calendar
    (
    [DateKey] [int] NOT NULL,
    [DateAltKey] [datetime] NOT NULL,
    [CalenderYear] [int] NOT NULL,
    [CalenderQuarter] [int] NOT NULL,
    [MonthOfYear] [int] NOT NULL,
    [MonthName] [nvarchar] (15) NOT NULL,
    [DayOfMonth] [int] NOT NULL,
    [DayName] [nvarchar] (15) NOT NULL,
    [DayofWeek] [int] NOT NULL,
    [DayNameOFWeek] [nvarchar] (15) NOT NULL,
    [FisealYear] [int] NOT NULL,
    [FisealQuarter] [int] NOT NULL
    )


    WHILE @LoopDate<=@EndDate

    BEGIN

    INSERT INTO Calendar
    ( DateKey ,
    DateAltKey ,
    CalenderYear ,
    CalenderQuarter ,
    MonthOfYear ,
    MonthName ,
    DayOfMonth ,
    DayName ,
    DayofWeek ,
    DayNameOFWeek ,
    FisealYear ,
    FisealQuarter
    )


    SELECT
    CAST(CONVERT(VARCHAR(8),@LoopDate,112) AS int) AS DateKey,
    @LoopDate AS DateAltKey,
    YEAR(@LoopDate) AS Year,
    DATEPART(qq,@LoopDate) AS Quarter,
    MONTH(@LoopDate) AS Month,
    DATENAME(mm,@LoopDate) AS MonthName,
    DAY(@LoopDate) AS DayNumber,
    DATENAME(dd,@LoopDate) AS DayName,
    DATEPART(dw,@LoopDate) AS WeekNumber,
    DATENAME(dw,@LoopDate) AS DayNameOFWeek,

    CASE WHEN MONTH(@LoopDate)<7 THEN YEAR(@LoopDate)
    ELSE YEAR(@LoopDate)+1 END FiscalYear,

    CASE
    WHEN MONTH(@LoopDate) IN (1,2,3) THEN 3
    WHEN MONTH(@LoopDate) IN (4,5,6) THEN 4
    WHEN MONTH(@LoopDate) IN (7,8,9) THEN 1
    WHEN MONTH(@LoopDate) IN (10,11,12) THEN 2
    END AS FiscalQuarter

    SET @LoopDate=DATEADD(dd,1,@LoopDate)
    END

    SELECT * FROM dbo.Calendar

    ReplyDelete

Post a Comment

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more

Apply paging in procedure

@intPageId int, @intPageSize int select * from ( Select     row_number() over (order by  TableA.intID asc ) r ,     TableA.intID,tableA.hName,TableB.Address  from TableA   Join TableB on TableA.intid = TableB.intId   ) A   where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize The query in bold text is for paging, and the normal text query is that which returns your records. OR SELECT * from PressRelease  order by CreatedDate desc OFFSET (@PageID-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
Read more