If you have date and time in a non standard format as strings, (date as dd/mm/yyyy, time as hh:mm) the following may help to convert the strings to a date that can then be managed. Thanks to Mike Cotrone and Dave S for their contributions to this forum.
Function convertToDateTime(date As String, time As String) As date
Dim d As New Date
Dim converted As Boolean
//convert string from passed format to standard format for SQL date time converted = ParseDate(date,d)
// now we can use SQLdateTime to add the time
// Gets and sets the date in SQL date format, YYYY-MM-DD. Passing a bad string will result in an UnsupportedFormatException.
d.SQLDateTime = Str( d.Year) + “-” + Str( d.month) + “-” + Str( d.day) + " " + time +":00"
return d
Philip, I don’t see that you use the string date in your function.
You need to get dd, mm and yyyy from date to do:
d.SQLDateTime = yyyy + "-" + mm + "-" + dd + " " + time + ":00"
sometimes dd/mm/yyyy is in fact 29/5/19 (not using 4 digits for the year).
Edit: I don’t know if date or time are reserved, good thing that I usually use Spanish words, fecha As String, hora As String
Edit2: If I’m sure the format is constant, in other words, no single digit day/month/hour/minute or 2 digit year, I could use:
[code]Function convertToDateTime(date As String, time As String) As date
Dim d As New Date
Dim converted As Boolean
//convert string from passed format to standard format for SQL date time
converted = ParseDate(date,d)
// now we can use SQLdateTime to add the time
// Gets and sets the date in SQL date format, YYYY-MM-DD. Passing a bad string will result in an UnsupportedFormatException.
d.SQLDateTime = Str( d.Year) + “-” + Str( d.month) + “-” + Str( d.day) + " " + time +":00"
return d
End Function[/code]
Somehow I think a simple EOL was dropped from the original copy.
However you should raise an exception if converted is false.
Thanks for the input.
Sorry about the bad formatting on the original post
Yes
converted = ParseDate(date,d)
is on a separate line.
Once the date and time are converted from strings, they can be used for time difference calculations, but I have found that a real mind bender. I think I have it working, but I needed to. take into account leap years and that took a while.
Dim testDate As New Date( d.year, 2, 29 )
Dim isLeapYear As Boolean = False
isLeapYear = testDate.Day = 29 // returns true if February has 29 days, false if it does not.
'If isLeapYear Then
'MsgBox(Str(testDate.Year) + " is a leap year")
'end if
Why don’t you convert, and subsequently store, your input date/time to seconds since the unix epoch. Then time difference calculations become trivial. Further, you can then let the user decide how they want their date/time displayed. I let the user decide between three date formats and two time formats . How a date/time looks is a display issue and should be entirely separate from internal representation or calculation.
You can feed the seconds into a Xojo date and it will convert that for you.
Here is a link that explains the issues: https://www.mathsisfun.com/leap-years.html
How to know if it is a Leap Year:
Leap Years are any year that can be exactly divided by 4 (such as 2012, 2016, etc)
except if it can be exactly divided by 100, then it isn’t (such as 2100, 2200, etc)
except if it can be exactly divided by 400, then it is (such as 2000, 2400)
I am playing with this as I haven’t found “date time calculations trivial”, as I think there might be a need to take into account the leap years if we are to display the number of days different.
e…g. How do we get this result? elapsed time = 5 years 3 months 16 days 17 hours 5 minutes and 35 seconds?
Well that and the fact that you need to know whether or not the start and end dates were within daylight savings time… whose start and end dates are different by location and have changed over the years. Arizona doesnt observe it at all for instance.
[quote=440414:@Philip Cumpston]Here is a link that explains the issues: https://www.mathsisfun.com/leap-years.html
[/quote]
And there is the (in)famous Software Performance Report response from Stan Rabinowitz to a bug report filed in 1983 about leap year calculations on VAX/VMS. Looking back at it from 36 years forward, it’s interesting that he mentions leap seconds as if they weren’t a big deal, yet a few years ago I had to help one of my customers determine what the system clock would return during a leap second on a system tied to a clock source that would include the leap second (sorry, don’t remember the answer now).