How To Get Previous Date/Next Date in SQL Server

--How To Get Previous Date/Next Date

     If you are working on date related queries, you may have came across scenarios where you wanted find out what is the immediate previous date or next date.

         In the following example I have explained how to do it. 

. 

CREATE TABLE DEPT (Srno INT,Department VARCHAR(100),CreatedDate   DATETIME)

INSERT INTO DEPT VALUES

(1,'HR',' 07/12/2012 10:30'),

(2,'HR','07/12/2012 10:41') ,

(3,'HR','08/12/2012 07:08'),

(4,'Development','07/12/2012 10:30') ,

(5,'Development','07/12/2012 11:33') ,

(6,'Testing',' 07/12/2012 17:18') ,

(7,'Testing',' 07/12/2012 10:31') ,

(8,'Testing','07/12/2012 11:38')

SELECT * FROM DEPT

--query output

Srno        Department   CreatedDate            

----------- ------------ -----------------------

1           HR           2012-07-12 10:30:00.000

2           HR           2012-07-12 10:41:00.000

3           HR           2012-08-12 07:08:00.000

4           Development  2012-07-12 10:30:00.000

5           Development  2012-07-12 11:33:00.000

6           Testing      2012-07-12 17:18:00.000

7           Testing      2012-07-12 10:31:00.000

8           Testing      2012-07-12 11:38:00.000

--get Next Date

SELECT T1.Srno,T1.Department,T1.CreatedDate,MIN(T2.CreatedDate) AS NextDate

FROM DEPT T1

LEFT OUTER JOIN DEPT T2

ON T1.Department=T2.Department AND T1.CreatedDate<T2.CreatedDate

GROUP BY T1.Srno,T1.Department,T1.CreatedDate

--Result

Srno        Department   CreatedDate             NextDate

----------- ------------ ----------------------- -----------------------

1           HR           2012-07-12 10:30:00.000 2012-07-12 10:41:00.000

2           HR           2012-07-12 10:41:00.000 2012-08-12 07:08:00.000

3           HR           2012-08-12 07:08:00.000 NULL

4           Development  2012-07-12 10:30:00.000 2012-07-12 11:33:00.000

5           Development  2012-07-12 11:33:00.000 NULL

6           Testing      2012-07-12 17:18:00.000 NULL

7           Testing      2012-07-12 10:31:00.000 2012-07-12 11:38:00.000

8           Testing      2012-07-12 11:38:00.000 2012-07-12 17:18:00.000

--get Previous Date

SELECT T1.Srno,T1.Department,T1.CreatedDate,MAX(T2.CreatedDate) AS PreviousDate

FROM DEPT T1

LEFT OUTER JOIN DEPT T2

ON T1.Department=T2.Department AND T1.CreatedDate>T2.CreatedDate

GROUP BY T1.Srno,T1.Department,T1.CreatedDate

--Result

Srno        Department     CreatedDate             PreviousDate

----------- -------------- ----------------------- -----------------------

1           HR             2012-07-12 10:30:00.000 NULL

2           HR             2012-07-12 10:41:00.000 2012-07-12 10:30:00.000

3           HR             2012-08-12 07:08:00.000 2012-07-12 10:41:00.000

4           Development    2012-07-12 10:30:00.000 NULL

5           Development    2012-07-12 11:33:00.000 2012-07-12 10:30:00.000

6           Testing        2012-07-12 17:18:00.000 2012-07-12 11:38:00.000

7           Testing        2012-07-12 10:31:00.000 NULL

8           Testing        2012-07-12 11:38:00.000 2012-07-12 10:31:00.000

Let me know if you know any other methods.