Wording with dates

[ClueLess]

Hi

I'm working on an application involving dates but can't find a way to make this to work:
1. An event is recorded in a Data Base with a specific date
2. Every day the program runs, read the data base date, and add one month to the date
3. Compare the result date with the "today" date
4. If the "today" date is equal or bigger the result date, It raises a flag

Can someone help me with this?

Thank You

[Lunasole]

Hi. It should be simple and something like this (see help for Date library)

Code: Select all

; Date means date you stored somewhere
Define Date

; This adds +1 month to a date
NewDate = AddDate(Date, #PB_Date_Month, 1)

; Compare today date with calculated
If Date() >= NewDate
	; raise flag
EndIf

[ClueLess]

Thank You
I have an idea now how to do it.

[infratec]

Hi,

it depends on your database. For SQLite:

Code: Select all

UseSQLiteDatabase()


DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  
  
  SQL$ = "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, targetdate INTEGER)"
  If DatabaseUpdate(DB, SQL$) = 0
    Debug DatabaseError()
  EndIf
  
  For i = -4 To 4
    SQL$ = "INSERT INTO test (targetdate) VALUES (date('" + FormatDate("%YYYY-%MM-%DD", Date() - (86400 * 30) + (86400 * i)) + "'))"
    DatabaseUpdate(DB, SQL$)
  Next i
  
  
  SQL$ = "SELECT id, date(targetdate) AS targetdate FROM test ORDER BY id"
  If DatabaseQuery(DB, SQL$)
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
  EndIf
  
  Debug "---------"
  
  SQL$ = "SELECT id, date(targetdate) AS targetdate FROM test WHERE date(targetdate, '+1 month') = date('now') ORDER BY id"
  If DatabaseQuery(DB, SQL$)
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1)
    Wend
  EndIf
  
  CloseDatabase(DB)
EndIf

Bernd

[ClueLess]

Thank You
It really works the way I need

[Blue]

it depends on your database. For SQLite:
Bernd

I'm curious, Bernd.
What will vary depending on the database ?
The results or only the code required to obtain the results ?

[infratec]

Hi,

the code itself is identical, but the SQL statements differ.
For example:
The date time functions are different and in postgresql.
You can not use BETWEEN for dates, you have to use >= <= or OVERLAP.
You have to use INTERVAL for adding a month.