5 Replies - 3535 Views - Last Post: 11 July 2012 - 01:55 PM Rate Topic: -----

#1 pkallberg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-March 12

SQLite months between two dates

Posted 11 July 2012 - 01:33 PM

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...

Is This A Good Question/Topic? 0
  • +

Replies To: SQLite months between two dates

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8373
  • View blog
  • Posts: 31,118
  • Joined: 12-June 08

Re: SQLite months between two dates

Posted 11 July 2012 - 01:33 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 pkallberg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-March 12

Re: SQLite months between two dates

Posted 11 July 2012 - 01:36 PM

View Postmodi123_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.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8373
  • View blog
  • Posts: 31,118
  • Joined: 12-June 08

Re: SQLite months between two dates

Posted 11 July 2012 - 01:47 PM

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)

Was This Post Helpful? 0
  • +
  • -

#5 pkallberg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-March 12

Re: SQLite months between two dates

Posted 11 July 2012 - 01:50 PM

View Postmodi123_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!
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8373
  • View blog
  • Posts: 31,118
  • Joined: 12-June 08

Re: SQLite months between two dates

Posted 11 July 2012 - 01:55 PM

... 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1