CODEWITHSUNDEEP
sqlsql-server
ahmed_eltot93
ahmed_eltot93

Reputation: 118

How to get start/end date from single date column from a SQL Server table

Could you please help me to get a solution for my issue. I am using a SQL Server database, and I want to get end date from the start date + month or day.

I have this table:

| ID | startdate |  month |  day  | 
| 1  | 2019-03-05|   3    | null  |
| 2  | 2019-03-05|  null  |   30  |

Desired output:

| ID | startdate |  month |  day  |  enddate  |
| 1  | 2019-03-05|   3    | null  |2019-06-05 |
| 2  | 2019-03-05|  null  |   30  |2019-04-04 |

Could you please help me to write a SQL query please?

Upvotes: 2

Views: 2530

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query using Dateadd() function. The DATEADD() function adds a time/date interval to a date and then returns the date.

create table tblMonth (Id int, startdate date, month int, day int)
insert into tblMonth values
( 1, '2019-03-05', 3, null),
(1, '2019-03-05', null, 30)

Select Id, startdate, month, day, DATEADD(MM, isnull(month,0), startDate) AS endDate
    from tblMonth

To add date with checking you can use case statement as shown below.

Select Id, startdate, month, day, 
    case when month is null then 
            DATEADD(dd, isnull(day, 0), startDate) 
        else
            DATEADD(mm, isnull(month,0), startDate) 
        end
    AS endDate
from tblMonth

Live Demo

Upvotes: 0

Mukesh Arora
Mukesh Arora

Reputation: 1813

You can use below query to get the desired result

declare @TestDate table
(id int, startdate date, [month] int, [day] int)

insert into @TestDate
values
(1, '2019-03-05', 3, null)

insert into @TestDate
values
(1, '2019-03-05',  null, 30)

select Id,
    startdate,
    [month],
    [day],
    dateadd(day, isnull(day, 0), dateadd(month, isnull([month], 0), startdate)) as enddate
from @TestDate

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

you can try below - using dateadd() function

select id, 
       startdate, 
       dateadd(case when month is null then dd else mm, coalesce(month,day),startdate) as enddate
from tablename

Upvotes: 0

Related Questions