none
to get all the date of the currrent year RRS feed

  • Question

  • I am using the following sql to get  all the dates of current year. But it list all the dates untill 2673-09-09. I want to get the dates of the current year and coming year only. How can I  change the sql to get the result. Please help

    SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER ( ORDER BY A.object_id ), convert(varchar(8), dateadd(dd,-1,dateadd(yy,datediff(yy,0,GETDATE()),0)),112)) AS DATE) AS DayDate 
    	FROM sys.objects A , sys.objects B  
    	order by DayDate
     

    With Thanks

    Pol


    polachan

    Wednesday, June 26, 2019 6:30 AM

All replies

  • SELECT DATEADD(DAY, number - 1, '20190101'),
            DATEPART(WEEK, DATEADD(DAY, number - 1, '20190101')),
            DATEPART(YEAR, DATEADD(DAY, number - 1, '20190101')),
            DATEPART(MONTH, DATEADD(DAY, number - 1, '20190101'))
    FROM master..spt_values
    WHERE type = 'P'
       AND number BETWEEN 1 AND 365;

    -----------------------------------------------------------
    with tmp(plant_date) as
    (
       select cast('20190101' as datetime)
       union all
       select plant_date + 1
         from tmp
        where plant_date < '20191231'
    )
    select*
      from  tmp
    option (maxrecursion 0)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 26, 2019 6:36 AM
    Answerer
  • Hi Polachan,

    Best suggestion ... build a calendar table that has a million uses in solving a myriad of date related database problems.

    A simple query, that is not hard-coded to a specific year, but always returns all dates for current year:

    with cte as
    (
    	select top 367 daynum = row_number() over (order by name) from master..spt_values
    )
    select
    	daynum,
    	dt = dateadd(day, daynum - 1,str(year(getdate())) + '-01-01')
    from
    	cte
    where
    	year(dateadd(day, daynum - 1,str(year(getdate())) + '-01-01')) <= year(getdate())
    order by
    	daynum;

    I hope this helps!

    --Dan

    Wednesday, June 26, 2019 1:19 PM
  • Use a number table:

    ;with  Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    Select dateadd(day,n-1,'2019-01-01') dt from nums
    WHERE dateadd(day,n-1,'2019-01-01')<'2021-01-01'

    Wednesday, June 26, 2019 1:26 PM
    Moderator
  • Hi Polachan,

    In my original example I was only returning the current year.  If you want to display the current year and the NEXT year only, change the TOP 367 to TOP 800 and change the where clause like below.

    I hope this helps.

    --Dan


    where
    	year(dateadd(day, daynum - 1,str(year(getdate())) + '-01-01')) <= ( year(getdate()) + 1)
    Wednesday, June 26, 2019 1:33 PM
  • "...to get the dates of the current year and coming year only"

    declare @beginningdate date=dateadd(year,datediff(year,0, getdate()),0)
    ;with  Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    Select dateadd(day,n-1,@beginningdate) dt from nums
    WHERE dateadd(day,n-1,@beginningdate)<Dateadd(year,2,@beginningdate)

    Wednesday, June 26, 2019 2:26 PM
    Moderator
  • Add this condition to your code:

    ;WITH CTE AS
    (
    SELECT 
    CAST(
         DATEADD(DAY,
    	         ROW_NUMBER() OVER ( ORDER BY A.object_id ), 
    			 convert(varchar(8), dateadd(dd,-1,dateadd(yy,datediff(yy,0,GETDATE()),0)),112)
    			 ) 
    	 AS DATE
    	) AS DayDate,
    YEAR(CAST(
         DATEADD(DAY,
    	         ROW_NUMBER() OVER ( ORDER BY A.object_id ), 
    			 convert(varchar(8), dateadd(dd,-1,dateadd(yy,datediff(yy,0,GETDATE()),0)),112)
    			 ) 
    	 AS DATE
    	)) AS V_YEAR
    FROM sys.objects A,sys.objects B  
    )
    SELECT 
    DayDate
    FROM CTE
    WHERE V_YEAR BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE())+1
    order by DayDate

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 27, 2019 6:40 AM
    Moderator
  • ;with cte as 
    (
    select distinct number, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS DATE) as dt from master..spt_values where number between 0 and 730
    )
    SELECT DATEADD(dd, number, dt) from cte
    Thursday, June 27, 2019 10:51 AM
  • --Recusrsive CTE for current year and next year dates
    WITH
    "DATES" AS(
        SELECT CAST(FORMAT(GETDATE(), 'yyyy') + '-01-01' AS DATE) AS "CDATE"
    UNION ALL
    SELECT DATEADD(DAY,1,"CDATE") FROM "DATES"
    WHERE "CDATE" < DATEADD(YEAR, 1, FORMAT(GETDATE(), 'yyyy') + '-12-31')
    )
    SELECT * FROM "DATES"
    OPTION (MAXRECURSION 1000);

    Tuesday, July 9, 2019 11:42 AM