Get the number of days between two dates

Question

I'm working on JupyterLab(SQL) and I want to get the difference of days between two columns.

The values of the columns are in the format YYYYMMDD but they aren't integers

How can I transform the columns to dates and then get the differences of days.

Answer 1

I'm not totally sure about JupyterLab, but in SQL Server you can use DATEDIFF() to calculate this. For example:

SELECT DATEDIFF(day, '2017/08/25', '2011/08/25') AS DateDiff;

See also:

https://www.w3schools.com/sql/func_sqlserver_datediff.asp

Answer 2

JupyterLab(SQL) supports SQLite, PostgreSQL, and MySQL databases:

MySQL:

SELECT DATEDIFF("20201005", "20201001");

PostgreSQL:

SELECT DATE_PART('day', AGE('20201005', '20201001'));

Answer 3

You did not mention the dbms so i am answering for oracle and postgres. You can use above answers but i like to convert them explicitly befor calculating difference. so here is for oracle -
TO_DATE('20170103','YYYYMMDD') - TO_DATE('20200103','YYYYMMDD')
postgres
EXTRACT(DAY FROM TO_TIMESTAMP('20160101', 'YYYYMMDD')-TO_TIMESTAMP('20150301', 'YYYYMMDD')