Precise DateTime from Database

Code Sharing
1

DatabaseColumn.DateTimeValue reads a standard SQLDateTime formatted string into a DateTime object. There are times when you need greater accuracy and for this, I use the following method which also optionally allows you to set the timezone. It is an extension method which means you need to paste this into a module and its scope needs to be global.

Public Function PreciseDateTime(Extends value As DatabaseColumn, Optional tz As TimeZone) As DateTime
  Var s As String = value.StringValue
  Var nanoseconds As Integer = s.NthField(".", 2).ToInteger
  Var dt As DateTime = DateTime.FromString(s.NthField(".", 1), Nil, tz)
  Var result As New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, Nanoseconds, tz)
  Return result
End Function

See Documentation for more information.

1 Like
2

Do you use this only for Windows? This line of code

dt = dt.FromString("2022-02-01 12:34:56.12345")

rise a RuntimeException on Mac. Do you have that type of strings?

3

Yes Windows only for me, but I’ve updated the code so it should now work on all OS’s.

Thanks for pointing this out @AlbertoD

2 Likes
4

Unless you have exactly the correct number of decimal places, that number is not nanoseconds. I would suggest:

Var nanoseconds as integer = val("0." + s.NthField(".",2)) * 1E9

4 Likes
5

Good point. I’ve only every used it for sorting.

Updated Code:

Public Function PreciseDateTime(Extends value As DatabaseColumn, Optional tz As TimeZone) As DateTime
  Var s As String = value.StringValue
  Var nanoseconds As Integer = val("0." + s.NthField(".",2)) * 1E9
  Var dt As DateTime = DateTime.FromString(s.NthField(".", 1), Nil, tz)
  Var result As New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, Nanoseconds, tz)
  Return result
End Function