Today, I have provided an article showing you how to calculate the difference between two dates of the same column or in different columns in SQL Server 2012. I have a table named Registration and this table has two relevant fields UserID and CreatedDate and Lastlogin. I have constructed a query that will give me the difference in days, between two dates in the same column or in different columns. Let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Creating Table in SQL Server Database
Now create a table named Registration with the columns UserID, LoginDate and LastLogin. Set the identity property=true for UserID. The table looks as in the following:
Calculate difference between two dates of the same column
To calculate the difference between two dates in the same column, we use the createdDate column of the registration table and apply the DATEDIFF function on that column. To find the difference between two dates in the same column, we need two dates from the same column. So suppose a1 is for the first date of the column createdDate and a2 is for the second date of the column createdDate.
Now check the following query for it. It works fine.
- SELECT a1.CreatedDate, DATEDIFF(DAY, a1.CreatedDate, a2.CreatedDate) as Difference from Registration a1 inner join Registration a2 on a2.UserID=a1.UserID+1
Now press F5 to see the difference between the two dates.
Calculate difference between two dates of different columns
To calculate the difference between two dates in different columns, we use the two columns createdDate and LastLogin of the registration table and apply the DATEDIFF function on these columns. To find the difference between the two dates in different columns, we need two dates from the different columns. So suppose createdDate is the first column and LastLogin is the second column in the Registration table.
Now check the following query for it. It works fine.
- SELECT a.userid, a.CreatedDate as Highdate, b.LastLogin as LowDate, DATEDIFF(day, a.CreatedDate, b.LastLogin) AS Diffs
- FROM (SELECT userid, CreatedDate, ROW_Number() OVER (Partition By userid ORDER BY CreatedDate) as RowNum FROM dbo.Registration) a
- INNER JOIN (SELECT userid, LastLogin, (ROW_Number() OVER (Partition By userid ORDER BY CreatedDate) -1)as RowNumMinusOne
- FROM dbo.Registration) b ON a.userid=b.userid
Now press F5 to see the difference between two column's dates, as in: