How do I find the difference between two dates with SQL?

Whether you’re:

  • calculating key supply chain indicators
  • trying to forecast orders/sales/revenue until the end of quarter/year,

you’re very likely to find yourself in the need of calculating the difference between two dates (timestamps)…

A way to go about it would be to use the datediff(‘years’, start date, current date) function.

The great thing about the datediff() function is that it is quite versatile when calculating the difference between dates.  Its syntax – datediff(‘time period’, start date, current date)  – allows it to calculate a time lapse ranging from a full year to just a second

That is, if you’re using Vertica or SQL Server.

datediff('year', '1988-06-21', Now()) as YearsBetweenDates,
datediff('quarter', '1988-06-21', Now()) as QuartersBetweenDates,
datediff('month', '1988-06-21', Now()) as MonthsBetweenDates,
datediff('day', '1988-06-21', Now()) as DaysBetweenDates,
datediff('hour', '1988-06-21', Now()) as HoursBetweenDates,
datediff('minute', '1988-06-21', Now()) as MinutesBetweenDates,
datediff('second', '1988-06-21', Now()) as SecondsBetweenDates,
timestampdiff('year', '2015-08-22', Now()) as YearsBetween,
timestampdiff('month', '2015-08-22', Now()) as MonthsBetween,
timestampdiff('week', '2015-08-22', Now()) as WeeksBetween,
timestampdiff('day', '2015-08-22', Now()) as DaysBetween,
timestampdiff('hour', '2015-08-22', Now()) as HoursBetween,
timestampdiff('minute', '2015-08-22', Now()) as MinutesBetwee
----------------------------------SQL Server-----------------------------------------------------------------------------------------------------
datediff(year, '1988-06-21', getdate()) as YearsBetweenDates,
datediff(quarter, '1988-06-21', getdate()) as QuartersBetweenDates,
datediff(month, '1988-06-21', getdate()) as MonthsBetweenDates,
datediff(day, '1988-06-21', getdate()) as DaysBetweenDates,
datediff(hour, '1988-06-21', getdate()) as HoursBetweenDates,
datediff(minute, '1988-06-21', getdate()) as MinutesBetweenDates,
datediff(second, '1988-06-21', getdate()) as SecondsBetweenDates
DaysBetween |YearsBetweenDates |QuartersBetweenDates |MonthsBetweenDates |DaysBetweenDates |HoursBetweenDates |MinutesBetweenDates |SecondsBetweenDates |
10764 |29 |117 |353 |10764 |258357 |15501421 |930085304 |
datediff(Now(), '1988-06-21') as DaysBetween,
timestampdiff(year, '1988-06-21', Now()) as YearsBetweenDates,
timestampdiff(quarter, '1988-06-21', Now()) as QuartersBetweenDates,
timestampdiff(month, '1988-06-21', Now()) as MonthsBetweenDates,
timestampdiff(day, '1988-06-21', Now()) as DaysBetweenDates,
timestampdiff(hour, '1988-06-21', Now()) as HoursBetweenDates,
timestampdiff(minute, '1988-06-21', Now()) as MinutesBetweenDates,
timestampdiff(second, '1988-06-21', Now()) as SecondsBetweenDates
DaysBetween |YearsBetweenDates |QuartersBetweenDates |MonthsBetweenDates |DaysBetweenDates |HoursBetweenDates |MinutesBetweenDates |SecondsBetweenDates |
10764 |29 |117 |353 |10764 |258357 |15501421 |930085304 |
age(Now(), '1988-06-21') as YearsBetweenDates
yearsbetweendates |
29 years 5 mons 19 days 21 hours 0 mins 39.466046 secs |

As you’ll have noticed above, datediff() in MySQL is slightly different – it always gives the difference in number of days and it has the following syntax – datediff (timstamp 1, timestamp 2). 

So, should you want MySQL to calculate any other time period, you should be using timestampdiff(timstamp, start date, current date)

Conveniently, Vertica supports both datediff() and timestampdiff()!

PostgreSQL is yet a completely different story – if you’d like to have the difference between two days, you need to use age(current date,  start date)which will give you the full difference in years, months, days, hours, minutes and seconds

Happy SQL-ing!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.