[pkallberg]

Hi,

I'm having a bit of difficulty determining the number of months between two date columns in my SQLite database. I've checked out this site thoroughly but can't tailor the query to my needs. I am able to check the number of days between dates by subtracting one julianday from another, but months are more complicated.

As an example, I've tried this query:

SELECT strftime('%m','2011-10-10') - strftime('%m','2012-01-01');

This query returns 9, although it should only return 1. I've tried playing around with modifiers and adding formats but can't get it to work the way I need it to.

Any ideas? Thanks!

In other words, I'd like this to work like the DATEDIF(start_date,end_date,"m") function in Excel, which I've seen used in SQL as well.

I got the dates mixed up in my first post, but even if you use 2012-01-01 as the first date and 2011-10-10 as the second, it will give you a difference of -9...

[modi123_1]

Quote

SELECT strftime('%m','2011-10-10') - strftime('%m','2012-01-01');

This query returns 9, although it should only return 1.

Why should it return one? 10 - 1 = 9.

[pkallberg]

modi123_1, on 11 July 2012 - 01:33 PM, said:

Quote

SELECT strftime('%m','2011-10-10') - strftime('%m','2012-01-01');

This query returns 9, although it should only return 1.

Why should it return one? 10 - 1 = 9.

Sorry I mixed up the dates... The code should be

SELECT strftime('%m','2012-01-01') - strftime('%m','2011-10-10');

and this returns -9. However, in reality, the number of months between these two dates is 1 whole month, ie. from November to December is a whole month, but then December to January doesn't become a whole month due to the days. I hope that makes more sense.

[modi123_1]

No.. actually the value should be -3... Remember you are asking it to find the value from just between the months with respects to their year.. no days involved. This concept of 'looking at whole months' is something you would need to introduce and program yourself. Jan to Dec = 1, Dec to Nov = 2, Nov to Oct = 3. Three.

It looks like you would need to do something with 'julianday'..

Quote

julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar)

[pkallberg]

modi123_1, on 11 July 2012 - 01:47 PM, said:

No.. actually the value should be -3... Remember you are asking it to find the value from just between the months with respects to their year.. no days involved. This concept of 'looking at whole months' is something you would need to introduce and program yourself. Jan to Dec = 1, Dec to Nov = 2, Nov to Oct = 3. Three.

It looks like you would need to do something with 'julianday'..

Quote

julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar)

Would you be able to link me to some documentation of how one programs in SQLite? I only just started working with this today so I'm pretty new. Or if you're feeling generous I'd really appreciate a quick explanation from you What would this kind of code look like? Thanks!

[modi123_1]

... it would look like you reading up on the julianday function... determining how to do convert your two dates to julian values.. then doing the math.. and then piecing back the results (probably through the julian day wiki page) and getting the months or days out of that.