CODEWITHSUNDEEP
sql-serversql-server-2008datetime
SKJ
SKJ

Reputation: 37

How do i display the end date from StartDate in sql server?

I am using SQL SERVER 2008. I have a table called Cut-Off table. This table only shows the StartDate of cut-off for every month. So i am creating a temp table that will store StartDate, EndDate, Month and Year. But i don't know how to get the EndDate with datetime.

TABLE

----------------------------------------------------------
StartDate              EndDate     Month   Year
----------------------------------------------------------
2014-12-15 00:00:00     NULL        12      2014
2015-01-26 00:00:00     NULL         1      2015
2015-02-26 00:00:00     NULL         2      2015
---------------------------------------------------------

EXPECTED OUTPUT 

--------------------------------------------------------------------
    StartDate              EndDate                   Month   Year
    ------------------------------------------------------------------
    2014-12-15 00:00:00     2015-01-25  23:59:99       12     2014
    2015-01-26 00:00:00     2015-02-25  23:59:99       1      2015
    2015-02-26 00:00:00     2015-03-25  23:59:99       2      2015
    ------------------------------------------------------------------

How i get the EndDate??

Upvotes: 1

Views: 2252

Answers (3)

mxix
mxix

Reputation: 3659

You can also do it with OUTER APPLY

select
    c.StartDate
    DATEADD(SECOND,-1,e.StartDate) EndDate
    DATEPART(MONTH,StartDate) [Month],   
    DATEPART(YEAR,StartDate) [Year]
from [Cut-Off] c
outer apply (
    select TOP 1
        e.StartDate
    from [Cut-Off] e
    where
        e.StartDate > c.StartDate
    order by
        e.StartDate
) e

Upvotes: 0

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

SAMPLE TABLE

CREATE TABLE #TEMP(DATES DATETIME,DATES DATETIME,[MONTH] INT,[YEAR] INT)

INSERT INTO #TEMP
SELECT '2014-12-15 00:00:00',     NULL,       12,      2014
UNION ALL
SELECT '2015-01-26 00:00:00',     NULL,       1,      2015
UNION ALL
SELECT '2015-02-26 00:00:00',     NULL,       2,      2015

QUERY

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY STARTDATE)RNO, STARTDATE
    FROM #TEMP
)
,CTE2 AS
(
    SELECT C1.STARTDATE,
    ISNULL(DATEADD(S,-1,C2.STARTDATE),DATEADD(S,-1,DATEADD(MONTH,1,C1.STARTDATE))) ENDDATE 
    FROM CTE C1
    LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1
)
UPDATE #TEMP
SET EndDate = CTE2.ENDDATE
FROM #TEMP T1
JOIN CTE2 ON T1.STARTDATE=CTE2.STARTDATE

RESULT

enter image description here

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Was unable to get 99 seconds in '23:59:99'

Try this:

CREATE TABLE #temp(StartDate datetime, EndDate datetime, Month int, Year int)

INSERT #temp values
  ('2014-12-15 00:00:00', NULL, 12, 2014),
  ('2015-01-26 00:00:00', NULL, 1, 2015),
  ('2015-02-26 00:00:00', NULL, 2, 2015)

UPDATE t
SET EndDate = DateAdd(m, DateDiff(m, 0, StartDate), '1900-02-25T23:59:59')
FROM #temp t

SELECT * FROM #temp

Result:

StartDate                EndDate                  Month  Year
2014-12-15 00:00:00.000  2015-01-25 23:59:59.000  12     2014
2015-01-26 00:00:00.000  2015-02-25 23:59:59.000  1      2015
2015-02-26 00:00:00.000  2015-03-25 23:59:59.000  2      2015

Edit:

If you want to calculate the EndDate from next StartDate, here is your UPDATE statement:

UPDATE t
SET EndDate = (SELECT top 1 DateAdd(s, -1, StartDate) FROM #temp 
               WHERE t.StartDate < StartDate 
               ORDER BY startdate)
FROM #temp t

Result:

StartDate                EndDate                  Month  Year
2014-12-15 00:00:00.000  2015-01-25 23:59:59.000  12     2014
2015-01-26 00:00:00.000  2015-02-25 23:59:59.000  1      2015
2015-02-26 00:00:00.000  null                     2      2015

Upvotes: 1

Related Questions