Hey guys,
this maybe a really obvious answer, or maybe me being lazy,
but is there a function about in the api or power basic where you can compare dates.
I have a list of dates in a database, and all i want to do is compare them with today's date to know if the date stored is in the future or in the past.
or whether i just go an around about way by comparing the year first etc...
Cheers Paul.
at the moment im using this...
If Val(StrReverse$(Parse$(date, "-",3)) + Parse$(date, "-",2)+Parse$(date, "-",1) ) _
> Val(StrReverse$(Parse$(Date$, "-",3)) + Parse$(Date$, "-",1)+Parse$(Date$, "-",2)) Then
not very readable, but reverses the year then have month day.
see which number is greater .
You will want to use "Julian" dates. Basically, it converts the date to a number and then you can compare them. Do a search on the PB forum for Julian and you should find some good examples. I have code (from Cheetah) that I can share. It is old code but should be usable. I can't post it here yet because I am not at my development computer.
Here is an example:
If D2J(DATE$) < D2J(DATETOCOMPARE$) THEN
' THE DATE IS OF THE FUTURE.
ELSE
THE DATE IS EITHER PRESENT OR PAST.
END IF
Here are 2 functions that you will find useful:
#INCLUDE "win32API.inc"
'================================================================
'Translates a date to Julian date.
'================================================================
FUNCTION D2J( BYVAL Date AS STRING ) AS LONG
LOCAL Year AS DWORD, Day AS DWORD, Month AS DWORD, Gregorian AS DWORD
LOCAL JulYear AS LONG, JulMonth AS LONG, Adjust AS LONG, JulDate AS LONG
Gregorian = 588829
Year = VAL(PARSE$(Date, ANY "-/\", 3))
Month = VAL(PARSE$(Date, ANY "-/\", 1))
Day = VAL(PARSE$(Date, ANY "-/\", 2))
IF ( Month > 2 ) THEN
JulYear = Year
JulMonth = Month + 1
ELSE
JulYear = Year - 1
JulMonth = Month + 13
END IF
JulDate = ( INT( 365.25 * JulYear ) + INT( 30.6001 * JulMonth ) + Day + 1720995 )
IF ( Day + ( 31 * ( Month + ( 12 * Year ) ) ) ) >= Gregorian THEN
Adjust = INT( 0.01 * JulYear )
JulDate = ( JulDate + 2 - Adjust + INT( 0.25 * Adjust ) )
END IF
FUNCTION = JulDate
END FUNCTION
'================================================================
'================================================================
' translates a Julian date to Normal Date.
'================================================================
FUNCTION J2D(BYVAL JulDate AS LONG ) AS STRING
LOCAL Year AS DWORD, Day AS DWORD, Month AS DWORD, Gregorian AS DWORD
LOCAL JulA AS LONG, Jul_Alpha AS LONG, JulB AS LONG, JulC AS LONG', TSDate AS LONG
LOCAL JulD AS LONG, JulE AS LONG, CalDate AS DWORD
Gregorian = 2299161
IF ( JulDate >= Gregorian ) THEN
Jul_Alpha = INT( ( ( JulDate - 1867216 ) - 0.25 ) / 36524.25 )
JulA = ( JulDate + 1 + Jul_Alpha - INT( 0.25 * Jul_Alpha ) )
ELSE
JulA = JulDate
END IF
JulB = JulA + 1524
JulC = INT( 6680! + ( ( JulB - 2439870 ) - 122.1 ) / 365.25 )
JulD = INT( 0.25 * JulC ) + ( 365 * JulC )
JulE = INT( ( JulB - JulD ) / 30.6001 )
Day = JulB - JulD - INT( 30.6001 * JulE )
Month = JulE - 1
IF ( Month > 12 ) THEN Month = Month - 12
Year = JulC - 4715
IF ( Month > 2 ) THEN Year = Year - 1
FUNCTION = FORMAT$(month, "00") & "-" & FORMAT$(Day, "00") & "-" & FORMAT$(Year)
END FUNCTION
'================================================================
And here are some other functions i have needed over the years, they require the two above:
'================================================================
FUNCTION DayOfWeek(BYVAL JulianDate AS LONG) AS LONG
IF ISFALSE(JulianDate MOD 7) THEN
FUNCTION = 7
ELSE
FUNCTION = (JulianDate MOD 7)
END IF
END FUNCTION
'================================================================
'================================================================
' Returns the number of days between one date and another.
'================================================================
FUNCTION SundaysBetween(Date1 AS STRING, Date2 AS STRING) AS LONG
LOCAL DateStart AS LONG
LOCAL DateEnd AS LONG
LOCAL Sundays AS LONG
LOCAL Day AS LONG
DateStart = D2J(Date1)
DateEnd = D2J(Date2)
FOR Day = DateStart TO DateEnd
IF DayOfWeek(Day) = 7 THEN INCR Sundays
NEXT Day
FUNCTION = Sundays
END FUNCTION
'================================================================
'================================================================
' Returns the number of days between one date and another.
'================================================================
FUNCTION DaysBetween(Date1 AS STRING, Date2 AS STRING) AS LONG
LOCAL DateStart AS LONG
LOCAL DateEnd AS LONG
DateStart = D2J(Date1)
DateEnd = D2J(Date2)
IF DateStart > DateEnd THEN SWAP DateStart, DateEnd
FUNCTION = (DateEnd-DateStart)
END FUNCTION
'================================================================
'================================================================
' Returns TRUE if the specified date is in a leap year.
'================================================================
FUNCTION Isleapyear (Date AS STRING) AS LONG
LOCAL Year AS LONG
Year = VAL(PARSE$(Date, ANY "-/\", 3))
IF ISTRUE((Year MOD 4) = 0) AND ISTRUE((Year MOD 100) > 0) OR _
ISTRUE((Year MOD 400) = 0) THEN FUNCTION = %TRUE
END FUNCTION
'================================================================
'================================================================
' Returns the number of the days for month in the specified date.
'================================================================
FUNCTION MonthDays(Date AS STRING) AS LONG
LOCAL Month AS LONG
Month = VAL(PARSE$(Date, ANY "-/\", 2))
IF (Month = 2) THEN
IF IsLeapYear(Date) THEN
FUNCTION = (VAL(READ$(Month))+1)
ELSE
FUNCTION = VAL(READ$(Month))
END IF
ELSE
FUNCTION = VAL(READ$(Month))
END IF
DATA 31,28,31,30,31,30,31,31,30,31,30,31
END FUNCTION
'================================================================
'================================================================
' returns the formatted date (Spanish)
'================================================================
FUNCTION Fecha(Date AS STRING) AS STRING
FUNCTION = FORMAT$(VAL(PARSE$(Date, ANY "-/\", 2))) & " de " & _
TRIM$(READ$(VAL(PARSE$(Date, ANY "-/\", 1)))) & _
IIF$(VAL(PARSE$(Date, ANY "-/\", 3))<2000, " de ", " del ") & _
PARSE$(Date, ANY "-/\", 3)
DATA Enero, Febrero, Marzo, Abril, Mayo, Junio, Julio
DATA Agosto, Septiembre, Octubre, Noviembre, Diciembre
END FUNCTION
'================================================================
Ahh I thought there would some sort of function for it.
Brill Cheers again Guys.
Maybe I'm missing something but from what was asked in the first message
it seems that all these conversions are overkill.
Why not simply reformat Date$ into a string variable in the
format YYYYMMDD once at the beginning of the program? Then format
the date field you want to compare to into a string variable in the
same format. A simple string compare and you have your answer. No
extra functions really needed.
But if you need to know the number of days between 2 dates then
by all means go the julian route.
Did I miss some past messages dealing with what's going on? ???
Thats kinda what I did myself to start with , with the string reverse. but the number of days was really useful for what i was doing.
cheers Paul.
I've built a set of function a long time ago that's converted various date formats to different date formats. One of the date formats I use is what I call a serial date which returns a long data type containing the number of days since 1/1/1980 (old dos format number). This makes it easy to compare or subtract one from the other to get the number of days. Of course the data I use doesn't look at birthdate of which you may want to pick another reference date to be much older but have the same idea.