Greetings!

I have a bunch of reports where I have to do a lot of dates comparison. Right now I am doing things like this,

strftime('%s', a.Finish_Date) > strftime('%s', a.Start_date)

but I am wondering if this would be more straight-thru and faster,

a.Finish_Date > a.Start_date

as in,

WITH Init (ProjID, pl_insert, pe_insert, ml_insert,today,eom)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid),
         (SELECT max(insertdate) FROM Project_Keytask_and_Milestones where projid = e.projid),
         (SELECT date('now')),
         (SELECT date('now','start of month','+1 month','-1 day'))
    FROM project_list e
    GROUP BY projid
 )
SELECT a.ProjID, a.CID, a.Project_Name AS PN, a.Manager AS CPM, a.PMO_Board_Report AS PMO_Rpt, 
       a.Project_Type AS PT, c.TaskName AS TN, c.StartDate AS SD, c.FinishDate AS FD, c.Status, c.Milestone, c.KeyTask,
       (
         CASE
          WHEN  -- Deliverables not completed
              c.FinishDate <= f.today 
            AND
              c.Status != 'Completed' 
            AND c.KeyTask = '1'
            AND c.Milestone = '1'
          THEN 'Deliverable not completed'
          WHEN  -- Deliverables soon to be completed
              c.FinishDate > f.today 
            AND
              c.FinishDate <= f.eom
            AND
              c.Status != 'Completed' 
            AND c.KeyTask = '1'
            AND c.Milestone = '1'
          THEN 'Deliverable soon to be completed'
          WHEN  -- Tasks not completed
              c.FinishDate <= f.today 
            AND
              c.Status != 'Completed' 
            AND c.KeyTask = '1'
            AND c.Milestone != '1' 
          THEN 'Task not completed'
          WHEN -- Tasks not Started
              c.StartDate <= f.today 
            AND
              c.Status = 'Not Started' 
            AND c.KeyTask = '1'
            AND c.Milestone != '1' 
          THEN 'Task not started'
          WHEN -- Milestones not completed
              c.FinishDate <= f.today 
            AND
              c.Status != 'Completed' 
            AND c.Milestone = '1'
            AND c.KeyTask  != '1'
          THEN 'Milestone not completed'
          WHEN -- Milestones soon to be completed
              c.FinishDate > f.today 
            AND
              c.FinishDate <= f.eom 
            AND
              c.Status != 'Completed' 
            AND c.Milestone = '1'
            AND c.KeyTask  != '1'
          THEN 'Milestone soon to be completed'
        END
       ) AS msg
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID
    AND a.Active != 'No'
    AND a.PMO_Board_Report != 'No' 
    AND a.Status == 'Acknowledged'
    AND b.FinCarryOver != 'y'
    AND b.MonthlyRpt = 'y'
    AND b.BudgetYear = 'YYYY'
LEFT JOIN Project_Keytask_and_Milestones AS c ON c.ProjID = b.ProjID
    AND c.Taskname NOT LIKE '%cost tracking%'
    AND
    (
      (  -- Deliverables
          (
            c.KeyTask = '1'
            AND
            c.Milestone = '1'
          )
          AND c.FinishDate <= f.eom
          AND c.Status != 'Completed' 
      )
      OR
      (  -- Keytasks Not Completed
          c.KeyTask = '1'
          AND c.Milestone != '1'
          AND c.FinishDate <= f.today
          AND c.Status != 'Completed' 
      )
      OR
      (  -- Keytasks Not Started
          c.KeyTask = '1'
          AND c.Milestone != '1'
          AND c.StartDate <= f.today
          AND c.Status = 'Not Started' 
      )
      OR
      (  -- MIlestones
          c.Milestone = '1'
          AND c.KeyTask != '1' 
          AND c.FinishDate <= f.eom
          AND c.Status != 'Completed' 
      )
    )
LEFT JOIN Init AS f
WHERE
    a.ProjID = f.projid
    AND a.InsertDate = f.pl_insert
    AND b.InsertDate = f.pe_insert  
    AND c.InsertDate = f.ml_insert
ORDER BY a.Manager, a.ProjID
;

There are others even longer, but you can see the idea. Thanks for your input.

What is the exact format of your stored dates and are they homogeneous?

Then you can just use < and >, exactly as you would compare strings.

If you have to do a lot of date comparison, store the date in a format which allows you to use < and >.

If you need to count days, store it as a number. If you don't, you might get away with YYYYMMDD or something like that.