8 Replies - 13116 Views - Last Post: 02 December 2016 - 05:59 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: 12446
  • View blog
  • Posts: 48,907
  • 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: 12446
  • View blog
  • Posts: 48,907
  • 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: 12446
  • View blog
  • Posts: 48,907
  • 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
  • +
  • -

#7 navyjax2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 30-January 09

Re: SQLite months between two dates

Posted 02 December 2016 - 05:27 PM

View Postmodi123_1, on 11 July 2012 - 01:55 PM, said:

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


This was NOT helpful. Had the same issue/question as the OP and without links, telling us essentially to "Google julianday" and shovel off wastes our time. Some of us already Googled to get here, as it was, only to find BS for an answer.

I found an example and thought I'd post for future Googlers... Julian Day is based on noon in Greenwich on November 24, 4714 B.C.

SELECT julianday('now') - julianday('1947-08-15');

gave

24549.5019360879

according to this site: http://www.w3resourc...e-julianday.php

Obviously it's tied to the day it was run, so it would be different, today.

To get the number of months difference, you'd have to convert that result - something like:

SELECT ((julianday('now') - julianday('1947-08-15'))/(365/12))

I would assume, to get the number of months. You could divide by 30 days instead of (365/12), I suppose, but I wanted to be more accurate.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 12446
  • View blog
  • Posts: 48,907
  • Joined: 12-June 08

Re: SQLite months between two dates

Posted 02 December 2016 - 05:45 PM

Thank you for following up four years later. I will take it under advisement.
Was This Post Helpful? 0
  • +
  • -

#9 navyjax2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 30-January 09

Re: SQLite months between two dates

Posted 02 December 2016 - 05:59 PM

View Postnavyjax2, on 02 December 2016 - 05:27 PM, said:

View Postmodi123_1, on 11 July 2012 - 01:55 PM, said:

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


This was NOT helpful. Had the same issue/question as the OP and without links, telling us essentially to "Google julianday" and shovel off wastes our time. Some of us already Googled to get here, as it was, only to find BS for an answer.

I found an example and thought I'd post for future Googlers... Julian Day is based on noon in Greenwich on November 24, 4714 B.C.

SELECT julianday('now') - julianday('1947-08-15');

gave

24549.5019360879

according to this site: http://www.w3resourc...e-julianday.php

Obviously it's tied to the day it was run, so it would be different, today.

To get the number of months difference, you'd have to convert that result - something like:

SELECT ((julianday('now') - julianday('1947-08-15'))/(365/12))

I would assume, to get the number of months. You could divide by 30 days instead of (365/12), I suppose, but I wanted to be more accurate.


This is probably better. You could run this in SQLite... it would give you the number of months:

SELECT CAST ((julianday('2012-01-01') - julianday('2011-10-10'))/(365/12) AS INTEGER)

Do the later date first, so you don't get negatives.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1