Search code examples
sqlsqliteselect-query

order by Time using current time?

I haven't knowledge about DBA . I am a ios developer i have one question for DBA .

`SELECT StartDate,Subject
 FROM tbl_Calendar
 WHERE StartDate BETWEEN ` date('2017-04-25') AND ('2017-04-26')

This is My Table Data

Ex : 1

if current time is 13:30:00

give me this order 3,4,5,2,1

Ex : 2

if current time is 12:35:00

give me this order 2,3,4,5,1

Ex : 3

if current time is 11:35:00

give me this order 1,2,3,4,5

This is my Table sql : 

  CREATE TABLE `tbl_Calendar` (
    `StartDate` datetime,
    `Subject`   ntext
  );

Any help will be much appreciated.

Solution

  • You could use CASE in your ORDER BY like this:

        SELECT 
        StartDate, Subject
    FROM   
        tbl_Calendar
    WHERE  
        StartDate BETWEEN DATE ('2017-04-25') AND ('2017-04-26')
    ORDER  BY 
        CASE WHEN ((StartDate < DATETIME('now', 'localtime')) AND (StartDate <> (SELECT StartDate FROM tbl_Calendar WHERE  StartDate < DATETIME ('now', 'localtime') ORDER  BY StartDate desc limit 1))) THEN DATETIME (StartDate, '+1 day') ELSE StartDate END;

    The subselect inside CASE will ignore the running event record and change only the previous ones.