Sql server Query help

Transact-SQL
1

Hi,
Need help to write sql query to get dates like below.(need to add 12 months to start date )
In one table data like below


id  NoYears    startdate
1   1year         2020-01-01
2    2 years     2021-03-01
3   3 years      2021 -05-01

Output

 id             year       startdate            enddate        
1             year1      2020-01-01        2021-12-31
2              Year2     2021-03-01       2022-02-28
2              Year2     2022-03-01       2023-02-28
3               Year3    2021 -05-01      2022 -04-30
3               year3     2022 -05-01     2023 -04-30
4               year3     2023 -05-01     2024 -04-31
2

Add one year, and then subtract one day, like this:
DATEADD(DAY, -1, DATEADD(YEAR,1,startdate))

1 Like
3

Thanks for your reply,
If we have only one date yes we can add this function directly, But For ex( as I mentioned 3 years and start date is 2021 -05-01 ) then need to derive 3rows like below

             Year3    2021 -05-01      2022 -04-30
               year3     2022 -05-01     2023 -04-30
               year3     2023 -05-01     2024 -04-31
4

The issue with your existing table is that the NoYears column is not numeric (i.e., character like "1year".) Instead if had just numbers, that would make the query much easier.

Also, you will need a numbers table in your database. If not, construct one as in the example below, where I am assuming NoYears column is numeric

;WITH Numbers(N)  AS
(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)
SELECT
    id,
    NoYears,
    DATEADD(YEAR,n.n-1,startdate),
    DATEADD(DAY,-1,DATEADD(YEAR,n.n,startdate))
FROM 
    YourTable t
    INNER JOIN Numbers AS n ON
        n.n <= t.NoYears
1 Like