Is it hard to sort dates based on string in tables?

[Asser]

Hey Mahmoud, Bert, Marino, Calm And et All,

Wanted a help to sort dates based on string in a table, any idea how to start

[Bert Mariani]

Hello Asser

Attached 

    -  SQL-Tutorial-2-date.ring

======================================

NOTE:  The SQLite   DATE   format  2018-12-30

 

---------------------------------------------------

// INSERT INTO some data VALUES using following queries.

// Note ID created in mixed order

    sql = " INSERT INTO Emp_Master

            VALUES   

                     ( 3,'Charlie',  'Czeck',    7766,   3,  DATE('now')),

                     ( 4,'Dog',      'Denmark',  5544,   2, '2018-11-12'),

                     ( 9,'Igloo',    'Italy',    6543,   2, '2017-04-03'),

                     (15,'Easy',     'England',  3322,   3, '2018-06-23'),

                     (17,'George',   'Germany',  1100,   2, '2017-08-01'),

                     ( 6,'Fox',      'France',   2211,   1, '2019-03-06'),

                     ( 1,'Able',     'Austria',  9988,   1,  DATE('now')),

                     ( 2,'Baker',    'Belgium',  8877,   2, '2017-12-31'),                  

                     ( 8,'Harry',    'Hungary',  7654,   3, '2018-11-30');

            "

    // EXECUTE SQLITE       

    sqlite_execute(oSQLite,sql)

--------------------

// Sort is ORDER BY with Column Position  DESC/ASC

    aDBLines = sqlite_execute(oSQLite, "SELECT * FROM  Emp_Master ORDER BY date DESC")

    DisplayDBLines( aDBLines, "SORT = Order BY date DESC")

**************************************************

SORT = Order BY date DESC

emp_id  first   last    salary  dept_id date

1       Able    Austria 9988    1       2019-07-24

3       Charlie Czeck   7766    3       2019-07-24

6       Fox     France  2211    1       2019-03-06

8       Harry   Hungary 7654    3       2018-11-30

15      Easy    England 3322    3       2018-06-23

2       Baker   Belgium 8877    2       2017-12-31

17      George  Germany 1100    2       2017-08-01

9       Igloo   Italy   6543    2       2017-04-03

**************************************************

[Asser]

Thanks very much Bert, gonna test it later :)

Hello, Bert, I have changed format of dates and ran the project, but it sorting wrong.

[Bert Mariani]

Hello Asser

If you look closely at the format you used

  6/4/2010

  6/3/2019

  5/2/2019

  28/5/2017

  25/2/2014

The sort is CORRECT .. looking at the left char ... 6 6 5 2 2

It was pointed out to you a couple of times  

USE the Year-Month-Day format ... YYYY-MM-DD  ( 2 digits for month and day NOT 1 )

Which is ALSO the SQLite format

    2019-07-24

    2019-07-24

    2019-03-06

    2018-11-30

    2018-06-23

    2017-12-31

    2017-08-01

    2017-04-03

[Asser]

What If i want do it as this format 2/1/2019 for example?  impossible?
it kinds boring using this format ( 2019-07-24 )

[Bert Mariani]

Use the proper format.

[Asser]

it kinda boring don't like it lol, wanted the format I like :D, if you know a way to change it, you can help if you want

[CalmoSoft]

Hello Asser,

You can reuse the next code.

Code:

load "stdlib.ring"

date = list(5)

date[1] = "6/4/2010"
date[2] = "6/3/2019"
date[3] = "5/2/2019"
date[4] = "28/5/2017"
date[5] = "25/2/2014"

see "before sorting:" + nl

see date

DateFormat = newlist(5,5)
DateFormatNew = newlist(5,5)
DateFormatEnd = list(5)

for n = 1 to len(date)
    DateFormat[n][n] = date[n]
next

for n = 1 to len(date)
    dd = substr(DateFormat[n][n], "/")
    dateday = substr(DateFormat[n][n],1,dd-1)
    d1 = substr(DateFormat[n][n],dd+1)
    dm = substr(d1, "/")
    datemonth = substr(d1,1,dm-1)
    d2 = substr(d1,dm+len(datemonth))
    dateyear = substr(d2,1,len(d2))
    if len(dateday) = 1
       dateday = "0" + dateday
    ok
    if len(datemonth) = 1
       datemonth = "0" + datemonth
    ok
    dateconv = dateyear + datemonth + dateday
    DateFormatNew[n] = dateconv
next

DateFormatNew = sort(DateFormatNew)

for n = 1 to len(date)
    dat = substr(DateFormatNew[n],7,2)
    dat = dat + "/" + substr(DateFormatNew[n],5,2)
    dat = dat + "/" + substr(DateFormatNew[n],1,4)

    if left(dat,1) = 0

       dat = right(dat,len(dat)-1)

    ok

    dat = substr(dat,"/0","/")

    DateFormatEnd[n] = dat  
next

see "after sorting:" + nl
see DateFormatEnd

Output:

before sorting:

6/4/2010

6/3/2019

5/2/2019

28/5/2017

25/2/2014

after sorting:

6/4/2010

25/2/2014

28/5/2017

5/2/2019

6/3/2019

Greetings,

Gal Zsolt

(~ CalmoSoft ~)

[Asser]

Working great!, Thank you very much!. Gonna add it later into my project ;)

[CalmoSoft]

Hello Asser,

Thanks for yourk kind words

Greetings,

Gal Zsolt

(~ CAlmoSoft ~)

[Asser]

Hey Calm, can you please reuse it for table? thx

[CalmoSoft]

Hello Asser,

I try to reuse for a table

but it will take a time.

[Asser]

take your time, calm, thx

[CalmoSoft]

Hello Asser,

Here is the early version of Date Format for Table.

Now it works properly once.

Please fill the cells and click on DATE button.

I am still working on it.

[Asser]

Thanks Calm, for the great work you do

[CalmoSoft]

Hello Asser,

Thanks for your kind words.

[CalmoSoft]

Hello Asser,

I have developed Date Format for Table.

Now the program works properly.

Please test the program.

[Asser]

Hey Welcome, thanks for the hard work you do, gonna test it well then tell you.

[CalmoSoft]

Hello Asser,

Thanks for your kind words.

Greetings,

Gal Zsolt

(~ CalmoSoft ~)

)

[Bert Mariani]

Hello Calmo

[Bert Mariani]

Line 156

Error (E2) : Out of Memory!

In substr() In function pdatesort() in file C:/MyStuff/TestOthers/CalmoSoftDateSort.ring

called from line 132  In function psort() in file C:/MyStuff/TestOthers/CalmoSoftDateSort.ring

called from line 85  In function sort2() in file C:/MyStuff/TestOthers/CalmoSoftDateSort.ring

called from line 11079  In qapp_exec() In method exec() in file C:\ring\extensions\ringqt\guilib\ring_qt.ring

called from line 64  in file C:/MyStuff/TestOthers/CalmoSoftDateSort.ring

[CalmoSoft]

Hello Bert et Asser,

Please use d/m/yyyy (30/7/2019) date fprmat

At the starting program you mentioned that you want to use this format..

[Asser]

Hey Calm, Thanks very much for the hard work, everything is working fine :) great

[CalmoSoft]

Hello Asser,

Thanks for your kind words.

Greetings,

Gal Zsolt

(~ CalmoSoft ˇ)

[Asser]

You are welcome <3

[Asser]

Hi Calm, could you add some comments on each function so i can understand what I read, thanks :)

[CalmoSoft]

Hello Asser,

I added some comments on functions.

Please tell me if you do not understanding something.

Greetings,

Gal Zsolt

(~ CalmoSoft ~)

[Asser]

Ok i will send you in private