conversion of date and time strings to SQLdate

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

End Function

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:

d.SQLDateTime = fecha.Right(4) + "-" + fecha.mid(4,2) + "-" + fecha.left(2) + " " + hora + ":00"

where fecha = “dd/mm/yyyy” (example “23/03/2003”) and hora = “hh:mm” (example “04:53”)

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

@Wayne Golding : you don’t use the time parameter ?

Isn’t time handled further down the code? All I did was add an EOL so parsedate was actually called, just trying to be kind to a fellow developer.

oups yes sorry.

Thank you Wayne, I didn’t see the ParseDate.

Edit: This code

d.SQLDateTime = Str( d.Year) + "-" + Str( d.month) + "-" + Str( d.day) + " " + time + ":00"

can be changed to this

d.SQLDateTime = d.SQLDate + " " + time + ":00"

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.

Function isLeapYear(d As Date) As Boolean

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

return isLeapyear

End Function

easier way :

Function isLeapYear(d As Date) As Boolean
return ((d.year mod 4) = 0)
End Function

I have a memory that some leap years are not divisible by 4. Will get back to you

d.year Mod 4 =0 And (d.year Mod 100 <>0 Or d.year Mod 400=0)

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.

my method is still working till Xojo 2100r1 … :wink:

except if someone trying to analyze the year 2100 in Xojo2019 :slight_smile:

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 doesn’t 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).

A few references about leap seconds…

https://www.nist.gov/pml/time-and-frequency-division/services/internet-time-service-its

https://ics-cert.us-cert.gov/sites/default/files/documents/Best_Practices_for_Leap_Second_Event_Occurring_on_31_December_2016_S508C.pdf

http://cyberjerry.info/calc/s1/scNoteS.html