RowSet Date time StringValue millisecond precision

When querying an MS SQL Server database, I usually parse the RowSet and store values into an array. This is by iterating the row and columns of the RowSet and getting the StringValue property of the DatabaseColumn when calling the ColumnAt method.
The trouble is, StringValue truncates Datetime fields from the database to second precision. How can I keep millisecond precision in the StringValue?

store SecondsFrom1970 = gmt/utc from the DateTime class
when you read the value back you set the timezone to local if required and reconstruct it.

Var epochFromDb As Double = rows.Column("mytimestamp").doublevalue
Var tz As Timezone = TimeZone.Current
Var myLocalDate As New DateTime(epochFromDb, tz)

Storing the SQLDateTime can cause issues, as it’s not 100% the time you gave it (as you have found out), also the Timezone may be unknown (since there is no information given) so make sure you define it yourself.

SecondsFrom1970 only gets second precision and doesn’t appear to get the milliseconds from the datetime value in the database?

it does since it’s a double everything after the “.” is nanoseconds on windows it would be less precise than on macos/linux.

SecondsFrom1970 store values precise and truncated to the “second”.

If you need fractions of second better store such fraction in another field or read the date/time with fractional seconds (the ending ".nnnnnnn " seven nanoseconds digits) as string and process it using your own routines as Xojo does not handle it writing or reading.

we use this mainly (nanoseconds) but FromString doesn’t know this concept.
Xojo’s from and to string is limited to max the year, month, day, hour, minute and seconds.

This is way you want to store the SecondsFrom1970 from where you got the date in the first place.
If you received it as string from somewhere you may want to re-construct using the DateTime constructor and parse the string yourself.

You could try this:

Public Function FromRFC1123(Extends dt as DateTime, input as String) As DateTime
  #Pragma Unused dt
  Var tmp As String = Input.Trim
  tmp = tmp.ReplaceAll(", ", " ")
  Var bits() As String = tmp.Split(" ")
  Var yearStr As String
  Var monthName As String
  Var dayName As String
  Var dayStr As String
  Var year As Integer
  Var month As Integer
  Var day As Integer
  Var timeStr As String
  Var hrStr As String
  Var minStr As String
  Var secStr As String
  Var hour As Integer
  Var minutes As Integer
  Var seconds As Integer
  Var tz As String
  For i As Integer = 0 To bits.LastIndex
    Select Case bits(i)
    Case "Sun", "Sunday", "Mon", "Monday", "Tue", "Tuesday", "Wed", "Wednesday", "Thu", "Thursday", "Fri", "Friday", "Sat", "Saturday"
      dayName = bits(i)
    Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
      monthName = bits(i)
      If (bits(i).IndexOf(":") >= 0) Then
        timestr = bits(i)
        Var parts() As String = timestr.Split(":")
        hrStr = parts(0)
        minStr = parts(1)
        secStr = parts(2)
      Elseif (bits(i).IndexOf("-") >= 0) Then
        // style 2 date with - between day mon year ?
        Var parts() As String = bits(i).Split("-")
        dayStr = parts(0)
        monthName = parts(1)
        yearStr = parts(2)
      Elseif dayStr = "" Then
        dayStr = bits(i)
      Elseif yearStr = "" Then
        yearStr = bits(i)
        tz = bits(i)
      End If
    End Select
  If (yearStr.Length <= 2) Then
    If Integer.FromString(yearStr) < 50 Then
      yearStr = "20" + yearStr
      yearStr = "19" + yearStr
    End If
  End If
  year = Integer.FromString(yearStr)
  Select Case monthName
  Case "Jan" 
    month = 1
  Case "Feb" 
    month = 2
  Case "Mar"
    month = 3
  Case "Apr"
    month = 4
  Case "May" 
    month = 5
  Case "Jun" 
    month = 6
  Case "Jul" 
    month = 7
  Case "Aug"
    month = 8
  Case "Sep" 
    month = 9
  Case "Oct"
    month = 10
  Case "Nov" 
    month = 11
  Case "Dec"
    month = 12
  End Select
  day = Integer.FromString(dayStr)
  hour = Integer.FromString(hrStr)
  minutes = Integer.FromString(minStr)
  seconds = Integer.FromString(secStr)
  Var gmtTZ As New TimeZone(0)
  Var tmpDt As New DateTime(year, month, day, hour, minutes, seconds, 0, gmtTZ)
  Return tmpDt
End Function

modify as you wish.

SecondsFrom1970 includes nanoseconds but using ToString/FromString only use seconds, no?

1 Like

Nope. It stores just seconds from 1970, not nanoseconds from 1970. The Object DateTime stores Nanoseconds from the constructor, I guess in a property apart.

Seconds from 1970 and a decimal part that includes nanoseconds, no? Your post made me think that if I do dt.SecondsFrom1970 I always get a double with .0

Easy to know. Let me check.

Maybe a difference between Windows and Mac, for example this line of code:

dt = dt.FromString("2022-02-1 12:34:56.013456")

I get:

An exception of class InvalidArgumentException was not handled. The application must shut down.
Exception Message: Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD

Your day is missing a zero

1 Like

Looks like ignoring nanoseconds


Interesting that it doesn’t change the result (fixing the 0). Interesting too that without the decimals and without the 0 (for 01) no complains:

Are you using Xojo 2022r4.1+ or an earlier one?

On Windows and the current Xojo it parses and ignores the nanoseconds

I guess that is a limit in how .ToString works, please change to something that adds format, like:


to get the MessageBox

2022r4.1 mac

What a mess. Completely unreliable and inconsistent.


I have also needed this requirement and I’ve posted the code I use at

I need to go. But ToString() should return the most complete and correct value possible to the last decimal.

All I can say is: don’t use the current implementation of nanoseconds, make your own.

Var now As DateTime = DateTime.Now
Var value As String = now.SecondsFrom1970.ToString(“0.00000000000”)

and you’ll get data
you can do now.SecondsFrom1970 * 1000 and store into uint64 and revert back once needed.