1

Is it possible to create a MapBasic SQL command to subtract one date column from another and get the number of days between the two (positive or negative values)?

I have found the following process in FME and ArcGIS but need a MapBasic solution

https://knowledge.safe.com/questions/4349/subtract-dates.html and Add or subtract days to date field using the field calculator

  • 1
    If they are actually just dates then you should be able to subtract them to get the difference in days without anything fancy. I suspect this won't work with Date/Time fields though. – T_Bacon Sep 20 '16 at 9:59
  • Or, of course you could use the Day() Month() and Year() functions to pull out the relevant data and do the calculations based on those. Gets a bit messy with leap years, different days per month, etc... so probably easier to just use the straight up subtraction i mentioned above. – T_Bacon Sep 20 '16 at 10:02
4

As Tom (@T_Bacon) wrote in the comments, this works nicely via the MapBasic window:

dim date1 as date
dim date2 as date
date1 = NumberToDate(20160912)
date2 = NumberToDate(20160913)
Print (date2 - date1) + " days"

The Message window will not say: "1 days"

So you can easily subtract two dates from each other.

Here's an example via SQL:

Select (ONEDATECOLUMN - ANOTHERDATECOLUMN) "DaysBetween"
   From SOMETABLE

Just make sure the columns are Date.

And here's an example where you subtract the date column from today:

Select (CurDate() - SOMEDATECOLUMN) "DaysBetween"
   From SOMETABLE
  • Thanks and rather than create a column with the current date can it be specified in the select statement? so you have Select ("Current date" - Other Date) ...where "Current date" is the command? to get the current date. – GeorgeC Sep 22 '16 at 1:22
  • Sure, see the new example I just added – Peter Horsbøll Møller Sep 22 '16 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.