# DateTime to SQLite

**URL:** <https://forum.xojo.com/t/datetime-to-sqlite/42307>\
**Category:** Databases\
**Created:** [March 3, 2018, 7:51am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307 "2018-03-03T07:51:23Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jukka\_Leino](https://forum.xojo.com/user_avatar/forum.xojo.com/jukka_leino/32/200_2.png) [@Jukka\_Leino](https://forum.xojo.com/u/Jukka_Leino)\
**Post date:** [March 3, 2018, 7:51am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/1 "2018-03-03T07:51:23Z")

</div>

Hi,

I have datetime as string like “02/03/2018 21:24”.  
I’m trying to store it to SQLite database where I have created datetime field.  
This is how I try to insert date:

```auto
// arvot(1) = "02/03/2018"
// arvot(2) = "21:24"
dim p() As String = Split(arvot(1),"/")
dim t() As String = Split(arvot(2),":")
Dim d1 As New Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), 0)
Dim d2 As New Xojo.Core.Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), Xojo.Core.TimeZone.Current)
dim lrow As new DatabaseRecord
lrow.Column("date1") = d1
lrow.Column("date2") = d2
vaakadb.InsertRecord("table", lrow)
```

This Xojo.Core.Date does not even build but Date does?  
What is the best way to insert this date to sqlite?  
Do I need DateTime field if I store epoch time as integer, or just integer field?  
I’m a bit confused with this 🙂

Thanks

Jukka

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [March 3, 2018, 8:14am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/2 "2018-03-03T08:14:48Z")

</div>

Before reading anything else, what is 02/03/2018 ?

February 2 or March 3 ?

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [March 3, 2018, 8:29am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/3 "2018-03-03T08:29:36Z")

</div>

[quote=375878:@Emile Schwarz]Before reading anything else, what is 02/03/2018 ?

February 2 or March 3 ?[/quote]  
Or February 3rd or March 2nd… 😛

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [March 3, 2018, 8:31am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/4 "2018-03-03T08:31:45Z")

</div>

[quote=375877:@Jukka Leino]Hi,

I have datetime as string like “02/03/2018 21:24”.  
I’m trying to store it to SQLite database where I have created datetime field.  
This is how I try to insert date:

```auto
// arvot(1) = "02/03/2018"
// arvot(2) = "21:24"
dim p() As String = Split(arvot(1),"/")
dim t() As String = Split(arvot(2),":")
Dim d1 As New Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), 0)
Dim d2 As New Xojo.Core.Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), Xojo.Core.TimeZone.Current)
dim lrow As new DatabaseRecord
lrow.Column("date1") = d1
lrow.Column("date2") = d2
vaakadb.InsertRecord("table", lrow)
```

This Xojo.Core.Date does not even build but Date does?  
What is the best way to insert this date to sqlite?  
Do I need DateTime field if I store epoch time as integer, or just integer field?  
I’m a bit confused with this 🙂

Thanks

Jukka[/quote]  
You could just store seconds from epoch and restore it from that. Don’t forget to store the time zone if you’re not using GMT!

---

<div class="post-metadata">

**Author:** ![John\_A\_Knight\_Jr](https://forum.xojo.com/user_avatar/forum.xojo.com/john_a_knight_jr/32/275_2.png) [@John\_A\_Knight\_Jr](https://forum.xojo.com/u/John_A_Knight_Jr)\
**Post date:** [March 3, 2018, 8:43am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/5 "2018-03-03T08:43:57Z")

</div>

SQLite DATETIME can understand several formats, but most often I use:

```auto
YYYY-MM-DD HH:MM:SS
```

It’s sortable and matches the Xojo Classic Date.SQLDateTime.  
Xojo.Core.Date has ToText, which produces the same result if you don’t provide a locale.

In your code, try these:

```auto
lrow.Column("date1") = d1.SQLDateTime
lrow.Column("date2") = d2.ToText
```

As @Greg O’Lone mentioned, you may need to account for the timezone.

---

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [March 3, 2018, 9:12am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/6 "2018-03-03T09:12:27Z")

</div>

You should use the Stardate to account for relativistic effects too … 😉

---

<div class="post-metadata">

**Author:** ![Jukka\_Leino](https://forum.xojo.com/user_avatar/forum.xojo.com/jukka_leino/32/200_2.png) [@Jukka\_Leino](https://forum.xojo.com/u/Jukka_Leino)\
**Post date:** [March 3, 2018, 9:27am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/7 "2018-03-03T09:27:48Z")

</div>

[quote=375878:@Emile Schwarz]Before reading anything else, what is 02/03/2018 ?

February 2 or March 3 ?[/quote]

It’s Yesterday

---

<div class="post-metadata">

**Author:** ![Jukka\_Leino](https://forum.xojo.com/user_avatar/forum.xojo.com/jukka_leino/32/200_2.png) [@Jukka\_Leino](https://forum.xojo.com/u/Jukka_Leino)\
**Post date:** [March 3, 2018, 9:41am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/8 "2018-03-03T09:41:44Z")

</div>

> [@375881:@Greg O'Lone](#):
>
> You could just store seconds from epoch and restore it from that. Don’t forget to store the time zone if you’re not using GMT!

If I store seconds, do I need to change date2 field type from datetime to integer?  
Like this?

```auto
lrow.IntegerColumn("date2") = d2.SecondsFrom1970
```

---

<div class="post-metadata">

**Author:** ![Jukka\_Leino](https://forum.xojo.com/user_avatar/forum.xojo.com/jukka_leino/32/200_2.png) [@Jukka\_Leino](https://forum.xojo.com/u/Jukka_Leino)\
**Post date:** [March 3, 2018, 9:44am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/9 "2018-03-03T09:44:59Z")

</div>

And btw. why this is ok:

```auto
lrow.DateColumn("date1") = d1 // classic date
```

And this is not:

```auto
lrow.DateColumn("date2") = d2 // xojo framework date
```

---

<div class="post-metadata">

**Author:** ![Alain\_Clausen](https://forum.xojo.com/user_avatar/forum.xojo.com/alain_clausen/32/877_2.png) [@Alain\_Clausen](https://forum.xojo.com/u/Alain_Clausen)\
**Post date:** [March 3, 2018, 10:38am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/10 "2018-03-03T10:38:13Z")

</div>

There are of course several ways to save a date and it depends on the use that you want to make. For my part, I often use a field with TotalSeconds as REAL and another one as TEXT only used for search.  
This approach has the following advantages:

- easily sort the Date field by TotalSeconds
- easily find a range between date1.TotalSeconds and date2.TotalSeconds
- display the date according to customer wishes (d.ShortDate, d.LongDate,…)

The second field allows you to save the date in different formats and therefore:

- build a fast search engine
- allow the user to find the data without having to know the format recorded in the db
- example for the search field: “\<02/03/2018 21:24\>\<March 2, 2018 21:24\>\<2018-03-02 21:24:00\>”

You can of course use d.SecondsFrom1970 instead of d.TotalSeconds.

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [March 3, 2018, 2:53pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/11 "2018-03-03T14:53:53Z")

</div>

[quote=375891:@Jukka Leino]And btw. why this is ok:

```auto
lrow.DateColumn("date1") = d1 // classic date
```

And this is not:

```auto
lrow.DateColumn("date2") = d2 // xojo framework date
```

Because the SQLiteDatabase plugin hasn’t been updated for the new framework.

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [March 3, 2018, 3:05pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/12 "2018-03-03T15:05:06Z")

</div>

First off SQLite does not have a DATE or DATETIME datatype. Regardless of the Xojo framework involved.

You should store Date/Time values in a manner that is consistent with not only SQLite but other DB engines as well (not only is it a good habit, but it makes things easier should you scale up to something else later)

A common misconceptions seems to be that just because the Database framework in Xojo indicates a datatype does not mean that SQLite has it as a native type (technically SQLite has NO “Datatypes”, but Affinitiys instead)

---

<div class="post-metadata">

**Author:** ![Massimiliano\_Chiodi](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/b782af/32.png) [@Massimiliano\_Chiodi](https://forum.xojo.com/u/Massimiliano_Chiodi)\
**Post date:** [May 22, 2018, 7:49am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/13 "2018-05-22T07:49:29Z")

</div>

In italy have the same format :  
dd/mm/yyyy or dd/mm/yyyy

I use this system

In textfield have a mask : “##/##/####”, cuetext “ **/** /\_\_\_\_” and in lostfocus event use ‘parsedate’ method

```auto
Sub LostFocus() Handles LostFocus
  if me.text <> "" then
    if isValidDate(me.text) = false then
     msgbox("La data indicata non una data valida!")
      me.text = ""
      me.SetFocus
    end if
  end if
End Sub
```

method ‘isValidDate’ code :

```auto
Public Function isValidDate(strDate as String) as Boolean
  // verifica se una data valida e ritorna true se valida o false se non valida
  
  if len(strDate) < 10 then
    return false
  else
    Dim mDate as Date
    if ParseDate(strDate, mDate) then
      return true
    else
      return false
    end if
  end if
  
End Function
```

Method for translate string to date ( locale format XOJO use control panel date/time settings )

```auto
Public Function StringToDate(extends s as String) as Date
  Dim mdate as Date
  if ParseDate(s, mDate) then
    return mdate
  end if
  
End Function
```

for example have a datetext

rsitem.field(“dateofpurchase”).datevalue = txtdatepurchase.text.stringtodate

if want ‘sql date’ use txtdatepurchase.text.stringtodate.sqldate

---

<div class="post-metadata">

**Author:** ![Massimiliano\_Chiodi](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/b782af/32.png) [@Massimiliano\_Chiodi](https://forum.xojo.com/u/Massimiliano_Chiodi)\
**Post date:** [May 22, 2018, 7:53am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/14 "2018-05-22T07:53:45Z")

</div>

For BYPASS computer date/time settings use :

from italian date format to sql date format

```auto
Public Function italiadata(datastring as String) as String
  ' converte una data italiana in data SQL 
  if datastring.trim = "" then
    return ""
  end if
  dim giorno as string
  dim mese as string
  dim anno as string
  dim stringadata as string
  
  dim datastringa() as string
  datastringa() = split(datastring,"/")
  
  giorno = datastringa(0)
  mese = datastringa(1)
  anno = datastringa(2)
  stringadata = anno + "-" + mese + "-" + giorno
  ' il formato restituito sar ANNO-MM-GG
  return stringadata
 
End Function
```

From date format to italian string date format

```auto
Public Function dataitalia(Extends data as date) as String
  if data is nil then
    data = new date
    dim giorno as integer = data.Day
    dim mese as Integer = data.Month
    dim anno as Integer = data.Year
    dim ritorno as string = protocollo(giorno,2,"0") + "/" + protocollo(mese,2,"0") + "/" + protocollo(anno,4,"0")
    Return ritorno
  else
    dim giorno as integer = data.Day
    dim mese as Integer = data.Month
    dim anno as Integer = data.Year
    dim ritorno as string = protocollo(giorno,2,"0") + "/" + protocollo(mese,2,"0") + "/" + protocollo(anno,4,"0")
    Return ritorno
  end if
  
End Function

Public Function protocollo(numero as variant, lunghezza as integer, fill as string) as String
  ' riempie il numero di x spazi vuoti all'inizio...
  dim numerotxt as string
  dim lungo as integer
  dim riempi as string
  dim riempito as string
  dim pos as integer
  
  ' 6 caratteri vuoti
  riempi = ""
  for pos = 1 to lunghezza
    riempi = riempi + fill
  next
 
  numerotxt = trim(str(numero))
  lungo = len(numerotxt)
  riempito = left(riempi,lunghezza-lungo)
  return riempito + numerotxt
End Function
```

---

<div class="post-metadata">

**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\
**Post date:** [May 22, 2018, 9:29am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/15 "2018-05-22T09:29:51Z")

</div>

I always convert dates to SecondsFrom1970 and store as double in SQLite for just the reasons that Dave S gave above. Only convert to a human-readable format when there’s a human asking for it.

---

<div class="post-metadata">

**Author:** ![Juan\_Man](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/a88e4f/32.png) [@Juan\_Man](https://forum.xojo.com/u/Juan_Man)\
**Post date:** [August 3, 2018, 6:55pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/16 "2018-08-03T18:55:24Z")

</div>

Hola !

Creo que estoy a punto de volverme loco con este lo.

No puede ser que algo tan simple como cambiar un tipo de dato String a Date o viceversa sea tanto problema. Son muchas lneas de cdigo y hasta ahora no me ha resultado nada.

Logre poner la fecha actual en un TextField con el siguiente cdigo:

// Coloca la fecha de hoy en el textfield con valor de fecha shortdate  
Dim d As New Date  
Dim displayDate As String = d.ShortDate //Convierte en String la variable d  
window1.FechaFld.text = displayDate  
//

Ahora, no se que hacer para guardar el valor String que captura el usuario como un valor tipo fecha en una tabla SQLite.

Que debo hacer ahora ? cranme que he intentado todo.

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [August 3, 2018, 7:02pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/17 "2018-08-03T19:02:02Z")

</div>

> [@](#):
>
> Hello !
> 
> I think I’m about to go crazy with this mess.
> 
> It can not be that something as simple as changing a data type String to Date or vice versa is such a problem. There are many lines of code and until now nothing has happened to me.
> 
> I managed to put the current date in a TextField with the following code:
> 
> // Place today’s date in the textfield with date value shortdate  
> Dim d As New Date  
> Dim displayDate As String = d.ShortDate // Convert the variable d to String  
> window1.FechaFld.text = displayDate  
> //
> 
> Now, I do not know what to do to save the String value that the user captures as a date type value in an SQLite table.
> 
> What am I supposed to do now ? Believe me, I’ve tried everything.

Use SQLDATE/SQLDATETIME format to store you Dates (as strings)

```auto
Dim d As New Date
Dim displayDate As String = d.SQLDate // Convert the variable d to String
window1.FechaFld.text = displayDate

Dim anotherDate as Date 
anotherDate.SQLDate = displayDate // convert from SQLDate format BACK to Xojo Date type
```

---

<div class="post-metadata">

**Author:** ![Juan\_Man](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/a88e4f/32.png) [@Juan\_Man](https://forum.xojo.com/u/Juan_Man)\
**Post date:** [August 21, 2018, 4:06pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/18 "2018-08-21T16:06:18Z")

</div>

Thank you Dave.

I’m starting in Xojo and I have a lot to learn.

---

<div class="post-metadata">

**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\
**Post date:** [August 21, 2018, 4:53pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/19 "2018-08-21T16:53:14Z")

</div>

> [@375893:@Alain Clausen](#):
>
> There are of course several ways to save a date and it depends on the use that you want to make. For my part, I often use a field with TotalSeconds as REAL and another one as TEXT only used for search.

Part of the issue here is that SQLite has no DATETIME type. Its types are INTEGER, REAL, TEXT, BLOB, or as NULL. See: [Datatypes In SQLite](https://www.sqlite.org/datatype3.html) for details.

[quote]  
This approach has the following advantages:

- easily sort the Date field by TotalSeconds
- easily find a range between date1.TotalSeconds and date2.TotalSeconds
- display the date according to customer wishes (d.ShortDate, d.LongDate,…)[/quote]

This is much the best approach, for the reasons given. Instead of trying to store a date in some text format that may not suit the user, store it in seconds and only for display convert to the user’s preferred format.

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [August 21, 2018, 7:54pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/20 "2018-08-21T19:54:12Z")

</div>

The advantage of storing the data as SQLDATE/SQLDATETIME format

- it is easily readable by “humans” for database debugging
- it is scalable to almost any existing database engine in existance (you may not stay with SQLite forever)
- it is directly sortable
- it does not rely on a given point in time for TotalSeconds [which may not convert to other operating systems)
- very easily converts to and from the Xojo DATE datatype
- can be used with the BETWEEN, GREATER and other comparisons in SQL

[Next page](https://forum.xojo.com/t/datetime-to-sqlite/42307.md?page=2)