SQLite Date sorting not getting correct result-CodePudding

My column is a date type and in the format of YYYY-MM-DD or 2022-12-01

When using the ORDER by clause October comes first then November then December followed by March etc

How do I get around this?

My Select statment is:

Select RequestedDate FROM PTLrequests Order by RequestedDate;

produces:

2022-10-13 2022-10-14 2022-10-29 2022-11-10 2022-11-11 2022-12-18 2022-12-19 2022-3-11 2022-3-12 2022-4-10 2022-4-11 2022-5-10 2022-5-11 2022-6-15 2022-6-16 2022-7-10 2022-7-11 2022-8-10 2022-8-11 2022-9-1 2022-9-5

CodePudding user response：

SQLite does not have a DATE datatype. Dates are stored as strings.

Make sure that your date format is sane in order to be able to sort properly. The only sane date format is yyyy-mm-dd (potentially followed by hh:nn:ss).

Dates like 2022-4-10 don't follow that format. Re-do the dates in your RequestedDate column so they have leading zeros where needed.

Read about the date formats and functions supported by SQLite.

This SQL can fix up your current data. Make sure you insert new data with the proper format.

update
  PTLrequests
set
  RequestedDate = substr(
    replace(
      replace(
        replace(
          replace(
            replace(
              replace(
                replace(
                  replace(
                    replace(
                      RequestedDate||'-',
                      '-9-', '-09-'
                    ),
                    '-8-', '-08-'
                  ),
                 '-7-', '-07-'
                ),
                '-6-', '-06-'
              ),
              '-5-', '-05-'
            ),
            '-4-', '-04-'
          ),
          '-3-', '-03-'
        ),
        '-2-', '-02-'
      ),
      '-1-', '-01-'
    ),
    0, 11
  )

An alternative would be to store dates as numbers, for example Unix timestamps or Julian dates, and only convert them into readable dates when needed for display purposes. This would make sorting, equality comparisons, and determining ranges very fast, but it might be inconvenient/cumbersome in other areas.