SQLite Turning a YYYY-MM-DD format into SUN/MON/TUE/WED/THU/FRI/SAT

0
votes

I have a set of 10 dates in the format YYYY-MM-DD (shown below). But I want to change these number format dates into either Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday through SQLite.

How function would I have to use to convert this from a numeric date to a day of the week date?

1992-08-14
1992-05-01
1992-04-01
1993-05-03
1993-10-17
1994-03-05
1993-10-17
1994-01-02
1994-11-15
1
sqlitedate
You haven't told us enough to even attempt an answer. - Jay

1 Answers

1
votes

By STRFTIME('%w', datecolumn) you get the day of the week of a date as a string:
'0' for Sunday, '1' for Monday, ..., '6' for Saturday

Use it like this:

SELECT
  CASE STRFTIME('%w', datecolumn) 
    WHEN '0' THEN 'SUN'
    WHEN '1' THEN 'MON'
    WHEN '2' THEN 'TUE'
    WHEN '3' THEN 'WED'
    WHEN '4' THEN 'THU'
    WHEN '5' THEN 'FRI'
    WHEN '6' THEN 'SAT' 
  END day,
  COUNT(*) count
FROM tablename
GROUP BY STRFTIME('%w', datecolumn)

See the demo.
Results:

| day | count |
| --- | ----- |
| SUN | 3     |
| MON | 1     |
| TUE | 1     |
| WED | 1     |
| FRI | 2     |
| SAT | 1     |