Android Question Format Date in TableView

[GeoffT660]

I'm trying to format a date in a tableview to MM/dd/yyyy from a DateTime SqlLite field that also displays 12:00AM which I want to remove for display purposes in the tableview. I'm using

tblIB.LoadSQLiteDB(sql1,"SELECT ESTDATE as Arrival ...

to load the table but can't figure out how to format the date in the select statement.

 

[Mahares]

Use strftime function
https://sqlite.org/lang_datefunc.html
SELECT strftime('%m/%Y/%d', estdate)

 

[KMatle]

Another way: Store ticks (DateTime(Now)).

 

[GeoffT660]

strftime('%m/%Y/%d', estdate)

Do I need any particular library for this? I had found the same sqlLite page and tried that but it returns nothing. This also doesn't work for formatting values supplied to edit text views which I still haven't figured out yet. I see the DateTime.DateFormat Function but have not figured that out.

 

[GeoffT660]

Another way: Store ticks (DateTime(Now)).

Can you please elaborate as I am supplying the value and also fairly new to B4a

 

[DonManfred]

fairly new to B4a

3 YEARS is new?

Can you please elaborate as I am supplying the value

    Log(DateTime.Now)

 

[klaus]

How are the data in the DateTime SqlLite field saved?

 

[GeoffT660]

3 YEARS is new?

    Log(DateTime.Now)

I have had b4a for that long but just started using it. That works great for DateTime.now but I'm supplying the date from a database and

DateTime.Cursor1.GetString("ESTDATE")

obviously wont work nor will it work loading the tableview so I am perplexed. The raw value is showing 11/10/2017 12:00 AM and I just want 11/10/2017. I never have a problem donating for clear, concise answers that save me time.

 

[GeoffT660]

How are the data in the DateTime SqlLite field saved?

As just the Date in a sqlLite DateTime Field. I just need to get a grip on all the Date/Time formatting functions and how to apply them to a TableView and EditText views. Are there any examples anywhere?

 

[klaus]

Sorry, but you don't answer my question!
SQLite has no DateTime data type!
The only data types in SQLite are: TEXT, REAL, INTEGER and BLOB.
SQLite has some internal functions which adapt data types from other database systems into one of the four basic types.
So, can you give an example of one DateTime filed?
If it's something like this: 2017/11/10 it is saved as a TEXT daty type.
Without this information it is difficult to give a concrete advice.
Then there are other information which could be intersting:
Where does the database come from ?
Did you define the database ?
Can you change the data types in the database ?
Is the database only used by you or programs written by you ?
As KMatle already suggested you could, should save the DataTime data as Ticks.
These can be retrieved with SQLite, eventhough SQLite doesn't use the same Ticks.
This is explained in the SQLite chapter in the B4A User's Guide, link in my signature.
Tick is a Long type integer representing the number of milliseconds elapased sine 1st january 1970.

 

[Knoppi]

Look here for samples how to use DateTime with sqlite
http://www.sqlitetutorial.net/sqlite-date/
You can store DateTime as TEXT, REAL, or INTEGER.

SQLite has no DateTime data type!

 

[klaus]

From the site of your link: SQLite does not support built-in date and/or time storage class.
That's what I mean with: SQLite has no DateTime data type!

Still from the same site: Instead, it leverages some built-in date and time functions to use other storage classes such as TEXT, REAL, or INTEGER for storing the date and time values.
This is what I meant with: SQLite has some internal functions which adapt data types from other database systems into one of the four basic types.
Perhaps this was too restrictive: from other database systems.
For me this question remains open: How are the data in the DateTime SqlLite field saved?
And more precisely this one: So, can you give an example of one DateTime filed?

 

[Mahares]

If your data is TEXT like : 11/07/2017 3:23 PM, the simplest way to ONLY extract the date part, use the substr function like this:

MyQuery="SELECT substr(estdate,1,10) as mdy from t1"  'if you have mm/dd,yyyy in date column: 11/07/2017 3:23 PM

If your data is like this: 6/7/2016 4:25 PM and your SQLite is newer and supports the instr and substr functions, use this code:

MyQuery="SELECT substr(estdate,1,instr(estdate,' ')-1) as mdy from t1" 'if you have m/d/yyyy in date column: 6/7/2016 4:25 PM

 

[GeoffT660]

Look here for samples how to use DateTime with sqlite
http://www.sqlitetutorial.net/sqlite-date/
You can store DateTime as TEXT, REAL, or INTEGER.

That is confusing as when opening the db in SQLite Maestro it shows a datatime field as the attached image suggest

. I have not reviewed all the other suggestions but if it's really a text field then I will try the substring functions. I was just hoping it was as easy to format as VB or many other languages but so far not or I'm missing something. https://www.dropbox.com/s/5srteghtxgply9a/TableStructure.jpg?dl=0

 

[GeoffT660]

From the site of your link: SQLite does not support built-in date and/or time storage class.
That's what I mean with: SQLite has no DateTime data type!

Still from the same site: Instead, it leverages some built-in date and time functions to use other storage classes such as TEXT, REAL, or INTEGER for storing the date and time values.
This is what I meant with: SQLite has some internal functions which adapt data types from other database systems into one of the four basic types.
Perhaps this was too restrictive: from other database systems.
For me this question remains open: How are the data in the DateTime SqlLite field saved?
And more precisely this one: So, can you give an example of one DateTime filed?

Here is a link to a picture of how the data is displayed. https://www.dropbox.com/s/1mk9llficjgxsjd/TableDates.jpg?dl=0 . I can't remember how I derived the tables several years ago but they are populated from a MS Sql server through a text file and imported to the sqlLite table after downloading to the device. Let me know what other information I can provide to best deal with my dates. Thanks.

 

[Mahares]

I'm trying to format a date in a tableview to MM/dd/yyyy from a DateTime SqlLite field that also displays 12:00AM which I want to remove

Here is a complete functioning code project that addresses your issue, It is tested and works. Sometimes you need to explain your questions more thoroughly:

Sub Process_Globals
    Dim SQL1 As SQL
End Sub

Sub Globals
End Sub

Sub Activity_Create(FirstTime As Boolean)   
    If FirstTime Then
        SQL1.Initialize(File.DirRootExternal,"test.db", True)
    End If
   
    createtables

    PopulateTable
   
    displayRecords
End Sub

Sub PopulateTable   
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 0,"11/10/2017 12:00 PM", 5))
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 1,"11/1/2017 1:00 AM", 10))       
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 2,"6/7/2016 4:25 PM", 10))
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 7,"10/30/2014 5:06:12 PM", 37))   
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 10,"10/28/2014 7:22:52 PM", 87))
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 11,Null, 46))
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 13,"8/8/2014 1:13 PM", 33))
        SQL1.ExecNonQuery2("INSERT INTO t1  VALUES (?,?,?)", Array As Object( 15,"", 81))
End Sub
   
Sub createtables
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS t1")
    SQL1.ExecNonQuery("CREATE TABLE t1 (id INTEGER PRIMARY KEY ,  estdate DATETIME, points INTEGER)")
'    SQL1.ExecNonQuery("CREATE TABLE t1 (id INTEGER PRIMARY KEY ,  estdate TEXt, points INTEGER)")  'this line works too
End Sub

Sub displayRecords
    Dim cursor1 As Cursor
    Dim MyQuery As String
    'BELOW STATEMNET WORKS If SQLite version supports instr function:
    MyQuery="SELECT substr(estdate,1,instr(estdate,' ')-1) as mdy from t1 " _
    & "WHERE estdate Is Not NULL AND estdate <> '' "    
    cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To cursor1.RowCount-1
        cursor1.Position=i
        Log( cursor1.GetString("mdy")  )
    Next
'End Sub

The log displays:
11/10/2017
11/1/2017
6/7/2016
10/30/2014
10/28/2014
8/8/2014

 

[Ricky D]

Do

Here is a link to a picture of how the data is displayed. https://www.dropbox.com/s/1mk9llficjgxsjd/TableDates.jpg?dl=0 . I can't remember how I derived the tables several years ago but they are populated from a MS Sql server through a text file and imported to the sqlLite table after downloading to the device. Let me know what other information I can provide to best deal with my dates. Thanks.

Do the substring thing