Hi,
one of my SQLite database columns called Date contains text string dates such as:
04/08/2014
03/09/2014
I sort the columns by Date Desc, but they appear in the wrong order, due to being sorted by the first 2 digits only.
Is it possible to somehow sort dates in this format correctly, and if so, could someone please advise me on the changes I need to make?
My Date database column type is TEXT (if that helps)?
Thank you all in advance.
Store your dates in SQLDate format (YYYY-MM-DD) or SQLDateTime format (YYYY-MM-DD HH:MM:SS), and they will be sorted properly.
http://documentation.xojo.com/index.php/Date.SQLDate
http://documentation.xojo.com/index.php/Date.SQLDateTime
In addition to Mark’s suggestion, to provide more database compatibly (SQLite doesn’t care), you should make your date database columns DATE or DATETIME not TEXT.
internally SQLite will use text to store dates and timestamps, but they will sort properly.
I am using a date component / plugin, which returns the selected date in the following format:
04/09/2014
I therefore, have no idea how to convert that into the SQLDate format, before saving it to my database 
04 being the date, 09 being the month, and 2014 obviously being the year 
You have to convert it to a proper DATE, DATETIME format. It will not work any other way. You can access the Year, Month and Day independently right? If so, you can make a method to construct the proper DATE or DATETIME value for SQL. If not, you’ll have to use the string and parse the values from that.
Ok, so basically I need to take the returned text string 04/09/2014, and then convert it into a different format.
What does the SQLDate format need to look like? Should I convert it to look like this 04-09-2014 and then save it into the database?
Thanks.
Tried that, but the listbox still sorts the date by the first 2 digits. I am using UK dates, which causes the problem.
Oh, listbox is another story. Your original question led me to believe you were trying to sort via SQLite. For a Listbox, it’ll fire the CompareRows event (http://documentation.xojo.com/index.php/ListBox.CompareRows). That will sort, by default, textually, which in your case is the 1st two digits. You need to implement that event.
Are you sorting the database, or a listbox? (or both?)
If you’re sorting a listbox, then display the value formatted how you want it in the cell, but store the SQL date into a cell tag or row tag, and use CompareRows to sort based on the SQL date.
Ok, this is getting confusing
I am using a date component, which returns the date for christmas day as 25/12/2014
This is what gets stored in the database 24/12/2014
This date is then loaded into my listbox, and I use ORDER BY Date when displaying the results in the listbox.
However, ORDER BY Date orders the dates in the listbox column by the first 2 digits.
I need to somehow reorder all the entries in the listbox’s date column, so that they are ordered in the UK date format (dd-mm-yyyy)
Sorry for my lack of understanding
Hope that made more sense.
Don’t store anything BUT sql format in the DB (and you may also want to add in something for the gmt offset since sql date format doesn’t currently include that)
then when you read a date you get back a Date instance (or can create one with all the right values)
internally NEVER use anything else
to show people you can use whatever short or long format they like with the LongDate & ShortDate properties
and you Sort based on “total seconds” - store a row tag or column tag with the Date object and implement the comp arrows event so you can sort however you want
Thanks everyone - I will look into this.
Richard this will convert your Calendar Format Date String into the SQLDate format.
I purposely loaded my manual “converted format”'ed string into a date object just for the sanity check to ensure it spits out of the date object as a 100% proper SQLDate.
HTH.
Format2SQLDate(inDate as String) as String
// TAKE THE INPUT DATE STRING AND SEPARATE EACH FIELD THUS REMOVING THE /'S
Dim Field1 as String = inDate.NthField("/",1)
Dim Field2 as String = inDate.NthField("/",2)
Dim Field3 as String = inDate.NthField("/",3)
// RECONSTRUCT THE DATE STRING IN THE PROPER FORMAT TO LOAD OUR DATE
Dim Converted2SQLDateFormat as String = Field3+"-"+Field2+"-"+Field1
// LOAD OUR NEW DATE WITH THE CORRECT NEWLY FORMATTED SELECTED DATE
Dim date1 as New Date
date1.SQLDate = Converted2SQLDateFormat
// LOAD SQLDATE INTO STRING TO PREPARE FOR RETURN
Dim RetDate as String = date1.SQLDate
Return RetDate
End FunctionAm working on this - thank you very much!