find latest date between created date and updated date in sql stored procedure

Question

I have two column in my table as created date and updated date.I want to find latest date from that.which ever is latest I want to considered that and filter it from input values.

My current query is :-

Select * 
from Emp E 
WHERE (E.USERID=@UserID) and (E.CDATE >= @FromDate AND E.CDATE <= @ToDate) order by qdate desc

What is the names of the fields in the table? I cannot see the "Updated date" in your question. — Morten, Apr 01 '19 at 10:49
@Morten right now I have considered only cdate but I have to consider which ever is latest date from cdate and update — Sharanaiyya Swami, Apr 01 '19 at 11:10
Logically, how could an update date ever be less than creation date? That should be impossible. — SMor, Apr 01 '19 at 11:48

score 1 · Answer 1 · answered Apr 01 '19 at 11:03

You can subtract fromDate and toDate with system date to find the latest date. And by using case you can filter based on the latest date. Like:

SELECT  *
FROM    Emp E
WHERE   ( E.USERID = @UserID )
        AND E.CDATE = CASE WHEN @fromdate - GETDATE() > @toDate - GETDATE()
                           THEN @fromdate
                           ELSE @toDate
                      END;

To find the latest date between two dates, you can use reference of the script below.

DECLARE @fromdate DATETIME, @toDate DATETIME, @date DATETIME
SET @fromdate = '2019-04-05'
SET @toDate = '2019-05-05'

SET @date = CASE WHEN @fromdate - GETDATE() > @toDate - GETDATE() THEN @fromdate
            ELSE @toDate
            END;
SELECT @date;

Md Abdul Mannan · Answer 2 · 2019-04-01T11:16:16.183

0

declare @cdate smalldatetime,@udate smalldatetime
Select CDATE=max(CDATE ) ,UDATE =max(UDATE ) into #tbldate
from Emp 

set @cdate =(select CDATE from #tbldate)
set @udate =(select udate from #tbldate)

if(@cdate>@udate)
begin 
 Select CDATE as Latest_Date from #tbldate
end
else
begin 
 Select UDATE as Latest_Date from #tbldate
end

//Run this by selecting all

edited Apr 01 '19 at 11:16

answered Apr 01 '19 at 10:55

Md Abdul Mannan

40
7

Mahesh · Accepted Answer · 2019-04-01T11:36:41.307

0

SELECT *
FROM Emp E
WHERE (E.USERID = @UserID)
    AND 1 = CASE 
        WHEN E.CDATE >= E.UDATE
            THEN CASE 
                    WHEN (
                            E.CDATE >= @FromDate
                            AND E.CDATE <= @ToDate
                            )
                        THEN 1
                    END
        WHEN E.CDATE < E.UDATE
            THEN CASE 
                    WHEN (
                            E.UDATE >= @FromDate
                            AND E.UDATE <= @ToDate
                            )
                        THEN 1
                    END
        ELSE 0
        END

edited Apr 01 '19 at 11:36

answered Apr 01 '19 at 10:58

Mahesh

198
2
16

What if both the dates are equal, that is not included in it. you may add that one from your end – Mahesh Apr 01 '19 at 11:05
if both dates are equal then considered any column – Sharanaiyya Swami Apr 01 '19 at 11:13
Done I have considered the first case itself. – Mahesh Apr 01 '19 at 11:37

score 0 · Answer 4 · answered Apr 01 '19 at 11:06

0

assuming update date is UDATE

select * from Emp E where (E.USERID=@UserID) and (SELECT MAX(V) from (values(E.CDATE), (E.UDATE)) MAX_DATE(V)) BETWEEN @FromDate AND @ToDate

answered Apr 01 '19 at 11:06

Ken

21
2

find latest date between created date and updated date in sql stored procedure

4 Answers4