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')
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.
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.