Selecting and Counting Specific Columns

[Eminence]

Alright. So now, I am trying to select only the dates from SQLite. I've tried this:

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, SUM(CASE WHEN attendance_status LIKE 'absent' THEN 1 ELSE 0 END) AS abs_count, SUM(CASE WHEN late_hours > 0 THEN 1 ELSE 0 END) AS late_count, SUM(CASE WHEN punch_out LIKE 'MISSING EOD' THEN 1 ELSE 0 END) as missing_eod, printf('%2.2f', 100.0 * SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 ELSE 0 END) / SUM(CASE WHEN schedule_status LIKE 'WORK' THEN 1 ELSE 0 END)) AS abs_rate, printf('%2.2f', 100.0 * SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 ELSE 0 END) / COUNT(CASE WHEN schedule_status LIKE 'WORK' THEN 1 ELSE 0 END) * 540) AS abs_in_mins FROM data_table WHERE date >= '2018/8/8' AND date <= '2018/8/17' GROUP BY supervisor"

What happens now is it doesn't result to anything. I've read on the SQLite date documentation that SQLite only supports date with the format of 'YYYY/MM/DD', is it possible that it is not showing because my from date's format lacks one digit (2018/8/8) instead of (2018/08/08)?

[jchd]

SQLite doesn't have a date (nor time) datatype. 'YYYY/MM/DD' is nothing else than a regular string (type CHAR or TEXT) hence will collate according to the column definition (or its default). When collating dates or times in text format it's necessary to comply with the ISO 8601 basic format, with fix-size fields where leading zeroes are important.

BTW

date >= '2018/8/8' AND date <= '2018/8/17'

can be writen date between '2018/08/08' AND '2018/08/17' but notice that the datetime built-in functions in SQL require a dash as separator instead of a slash.