SQL Server - How to get difference between two dates

If you are working as a SQL Professional then there is a lot of activities which are related to dates. You have to write SQL queries to find out the difference between two dates or need to get the next day, next month, next week, next quarter or next year to set some business logic. 

Business Scenario 

Suppose that you need to send an email alert to your customers to remind them to pay the invoice bill after 30 days of invoice generation as per their contract. Hence, you have to write an SQL based program to check all the unpaid invoices which comes under this conditions. 

Here, In your SQL based program (it may be a stored procedure which may be schedule to run on the daily basis on the defined schedule), you have to get the difference between the invoice date and current running date. 

If the difference result is matched with the contract with the customer then system will pull that invoice and send it to the customer as the email attachment. 

To full-fill the above business needs, you have to use some defined SQL Date related functions such as -

DateDIFF(interval, date1, date2) is used to get the differences between two dates and return numeric values such as difference in years, months, quarters, days, weeks, hours, minutes, seconds, milliseconds and nanoseconds etc. The interval of time is used to calculate the difference between date1 and date2. It can be one of the following values:

Value (any one of)	Explanation
year, yyyy, yy	Year interval
quarter, qq, q	Quarter interval
month, mm, m	Month interval
dayofyear	Day of year interval
day, dy, y	Day interval
week, ww, wk	Week interval
weekday, dw, w	Weekday interval
hour, hh	Hour interval
minute, mi, n	Minute interval
second, ss, s	Second interval
millisecond, ms	Millisecond interval

Examples

---- Difference in years SELECT DATEDIFF(year, '2012/04/28', '2014/04/28'); Result: 2 ---- Difference in months SELECT DATEDIFF(month, '2014/01/01', '2014/04/28'); Result: 3 ---- Difference in days SELECT DATEDIFF(day, '2014/01/01', '2014/04/28'); Result: 117 ---- Difference in hours SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45'); Result: 2 ---- Difference in minutes SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45'); Result: 165

To watch a demo video, please visit our YouTube channel -