Sort by Date

General
1

Hi,
This problem is driving me absolutely crazy!!!

NOTE - THESE ARE UK DATE FORMATS

I have an SQLite database column (type = text) which contains the following dates:
25/12/2014 (25th December 2014)
27/12/2014 (27th December 2014)
26/11/2014 (26th December 2014)

Due to SQLite having no Date type, I am having to use TEXT as the column type.
Could someone please tell me what I need to do in order to make these appear with the newest date at the top - like this:

27/12/2014 (27th December 2014)
25/12/2014 (25th December 2014)
26/11/2014 (26th December 2014)

I cannot work out what I need to do to the text string containing the date, in order to make the columns display in the correct order when I use ORDER BY Date Desc.

2

Always store dates in SQLDate format. It is designed to be able to sort correctly.

3

in my query, i convert the dd/mm/yyyy to yyyy-mm-dd and then sort the new column

SELECT BoughtDate, SUBSTR(BoughtDate,7,4) || ‘-’ || SUBSTR(BoughtDate,4,2) || ‘-’ || SUBSTR(BoughtDate,1,2) as cvDate
FROM tblStock
ORDER BY cvDate

4

Richard the date format you have above is not the SQLDate format. You are saving the Date in your DB as a SQLDate in a text data type db field?

5

i am saving the date as TEXT/VARCHAR in the database.

I have another system where i use YYYY-MM-DD and have to covert to the UK/US date when showing in the form.

6

Sorry Richard Duke, I was referencing Richard Summers in my question above :slight_smile:

7

[quote=144719:@Richard Summers]Hi,
This problem is driving me absolutely crazy!!!

Never ever store “short date” “long date” or any other localized date format
Only EVER put SQLDATETIME in a SQLite field regardless of the field type

And then you can sort by date and make sure your query ends with

ORDER BY Date(Datecolumn) desc

I would NOT name the column DATE since sqlite has a function called “date” which may get confusing

8

hahaha!!! both of us are Richard.

9

Richard,

  1. Change your date format to SQLDate in your calendar output.

  2. If you don’t want to use a SQL Statement to convert your SQLDate into the your format then use this function in between your database and your Listbox Population
    Database Select into a Record Set–> this function --> Your listbox population

Private Function(inSQLDate as String) as String

// THIS FUNCTION CONVERTS THE SQLDATE INPUT TO THE D/M/Y FORMAT
Dim convertedDate as String

Dim fieldY, fieldD, fieldM as String

fieldY = inSQLDate.NthField("-",1)
fieldM = inSQLDate.NthField("-",2)
fieldD = inSQLDate.NthField("-",3)

// REASSEMBLE DATE INTO THE APPROPRIATE UK FORMAT
convertedDate = fieldD+"/"+fieldM+"/"+fieldY

Return convertedDate

End Function

Also note if you are changing your listbox (SQL Update) then you need to reverse this function to convert back to SQLDate.

HTH

10

Did not know we use Date(DateColumn) in SQLite

11

Here is the function to convert back to SQLDate FYI.

convertToSQLDate (inSQLDate as String) as String
// THIS FUNCTION CONVERTS THE SQLDATE INPUT TO THE D/M/Y FORMAT
Dim convertedDate as String

Dim fieldY, fieldD, fieldM as String
fieldD = inUKDate.NthField("/",1)
fieldM = inUKDate.NthField("/",2)
fieldY = inUKDate.NthField("/",3)

// REASSEMBLE DATE INTO THE APPROPRIATE SQL DATE FORMAT 2014-11-20
convertedDate = fieldY+"-"+fieldM+"-"+fieldD

Return convertedDate

12

Once again - Mr Cotrone to the rescue :slight_smile:
Thanks Mate. In fact - thank you all :slight_smile: