CODEWITHSUNDEEP
sqlitestrftime
RHsln
RHsln

Reputation: 11

How does one use STRFTIME to calculate the difference between two dates, giving a length of time?

I'm working with a fictional database where I have to calculate the tenure of employees. So, I want to do 'now' - hiredate = tenure.

I've tried a few things but only Null is returned.

SELECT
    FirstName,
    LastName,
    HireDate,
    strftime('y%-m%-d%', HireDate) - strftime('y%-m%-d%','now') AS Tenure
FROM
    EMPLOYEES
ORDER BY
    Tenure

Upvotes: 1

Views: 908

Answers (2)

T0xicCode
T0xicCode

Reputation: 4931

strftime creates a string from a given (date)time value (string from time).

Instead, you should simply compare the julian days:

SELECT FirstName, LastName, HireDate, julianday('now') - julianday(HireDate) AS Tenure FROM EMPLOYEES ORDER BY Tenure

Upvotes: 1

forpas
forpas

Reputation: 164089

SQLite's date functions like strftime() work only with dates in the format YYYY-MM-DD.
If the column HireDate is in this format you can the difference of HireDate to the current date in seconds like this:

SELECT FirstName, LastName, HireDate, 
       (strftime('%s', CURRENT_DATE) - strftime('%s', HireDate)) AS Tenure 
FROM EMPLOYEES 
ORDER BY Tenure

because strftime('%s', some_date) returns the number of seconds passed since 1970-01-01 till some_date.

Or the same difference in days with julianday():

SELECT FirstName, LastName, HireDate, 
       (julianday(CURRENT_DATE) - julianday(HireDate)) AS Tenure 
FROM EMPLOYEES 
ORDER BY Tenure

because julianday(some_date) returns the number of days since noon in Greenwich on November 24, 4714 B.C. till some_date.

Upvotes: 0

Related Questions