# Precise DateTime from Database

**URL:** <https://forum.xojo.com/t/precise-datetime-from-database/74481>\
**Category:** Code Sharing\
**Created:** [February 13, 2023, 5:27pm UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481 "2023-02-13T17:27:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Wayne\_Golding](https://forum.xojo.com/user_avatar/forum.xojo.com/wayne_golding/32/165_2.png) [@Wayne\_Golding](https://forum.xojo.com/u/Wayne_Golding)\
**Post date:** [February 13, 2023, 5:27pm UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481/1 "2023-02-13T17:27:51Z")

</div>

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.

```auto
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](https://documentation.xojo.com/api/data_types/datetime.html#datetime-fromstring) for more information.

---

<div class="post-metadata">

**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\
**Post date:** [February 13, 2023, 5:41pm UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481/2 "2023-02-13T17:41:02Z")

</div>

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

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

```

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

---

<div class="post-metadata">

**Author:** ![Wayne\_Golding](https://forum.xojo.com/user_avatar/forum.xojo.com/wayne_golding/32/165_2.png) [@Wayne\_Golding](https://forum.xojo.com/u/Wayne_Golding)\
**Post date:** [February 13, 2023, 5:46pm UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481/3 "2023-02-13T17:46:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Paul\_Rodman](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_rodman/32/349_2.png) [@Paul\_Rodman](https://forum.xojo.com/u/Paul_Rodman)\
**Post date:** [February 13, 2023, 9:09pm UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481/4 "2023-02-13T21:09:07Z")

</div>

> [@Wayne\_Golding](#):
>
> `Var nanoseconds As Integer = s.NthField(".", 2).ToInteger`

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`

---

<div class="post-metadata">

**Author:** ![Wayne\_Golding](https://forum.xojo.com/user_avatar/forum.xojo.com/wayne_golding/32/165_2.png) [@Wayne\_Golding](https://forum.xojo.com/u/Wayne_Golding)\
**Post date:** [February 14, 2023, 12:31am UTC](https://forum.xojo.com/t/precise-datetime-from-database/74481/5 "2023-02-14T00:31:42Z")

</div>

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

Updated Code:

```auto
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

```