Question 30334 in Mysql Question posted in
The official MySQL documentation can be found here.

Comparing casted/converted/str_to_date values in CASE? – Mysql

I’m hurting for an answer on this one. I’m not great at SQL just yet, but I’m trying to write a select statement that finds dates within a certain range. My problem, I think, is that the date values are strings and it doesn’t compare properly. This is the code I’ve got so far.

SELECT CONCAT (suffix, " ", lname, " ", fname, " ", name) AS "Name",
    id AS "Member ID",
    CONCAT (address1, " ", address2, " ") AS "Address Line 1", 
    CONCAT (city, " ", state, " - ", zip)  AS "Address Line 2", 
        CASE
            WHEN STR_TO_DATE(regionexpires,'%d.%m.%Y') > 01/01/2023 THEN 'Yes'
            ELSE 'No'
        END AS "Subscriber?"
FROM roster
LIMIT 100000

I’ve tried casting, converting, and the str_to_date function – /’s, .’s, you name it. All 1090 results are No. These should definitely at least have a couple yeses. This is for MySQL, by the by.

Tags:

2

Answers


  1. 0

    IIRC, date’s get formatted like strings in MySQL. So something like WHEN STR_TO_DATE(regionexpires,'%d-%m-%Y') > '01-01-2023 THEN 'Yes' Notice the use of dashes in both the formatting string and the date value itself. Slashes might work, don’t have an instance in front of me that I can tests with though.

    Login or Signup to reply.
  2. 0

    You should never store date or time information as strings. Ideally you would take the time to correct this so that all your queries will be simpler in future.

    However to address the immediate issue please get used to concept of "date literal" which is how we give a SQL query a date. It is a good idea to also use the most common ISO 8601 format for these literals e.g. ‘2023-01-01’

    So, you comparison needs a valid "date literal" e.g.

    STR_TO_DATE(regionexpires,'%d.%m.%Y') > '2023-01-01'

    Notice that to convert you strings into date, you should use the format of how those strings have been stored. e.g. if your data looks like this:

    23.12.2022
13.04.2022

    Then STR_TO_DATE(regionexpires,’%d.%m.%Y’) results in a date and that date can then be compared to a valid "date literal"

    However if your data contains just 2 digit years or delimiters other than periods and/or they are in dmy or mdy sequence then you need a different str_to_date for each format your data holds. Plus strings can be invalid dates or even nothing like a date at all. This is why storing dates as strings is a bad idea.

    Further information aimed at changing that string column into a date column:

    CREATE TABLE roster (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  regionexpires VARCHAR(20)
);


    INSERT INTO roster (id, name, regionexpires)
VALUES
  (1, 'John Doe', '2022-12-31'),
  (2, 'Jane Smith', '31.12.2022'),
  (3, 'Bob Johnson', '2022.12.31'),
  (4, 'Alice Lee', '31-12-2022'),
  (5, 'Tom Brown', '2022/12/31'),
  (6, 'Sara Green', '12/31/2022'),
  (7, 'Mike Davis', '2022-31-12'),
  (8, 'Emily White', '31-2022-12'),
  (9, 'Mark Black', '2022-12-32'),
  (10, 'Lisa Grey', '2022-13-31');


    Records: 10  Duplicates: 0  Warnings: 0

    -- DDL to alter the "roster" table with a date column "expiry_date"
ALTER TABLE roster ADD COLUMN expiry_date DATE;

    Records: 0  Duplicates: 0  Warnings: 0

    -- Temporarily disable strict mode for the current session
SET sql_mode = '';


    UPDATE roster 
SET expiry_date = CASE 
    WHEN STR_TO_DATE(regionexpires, '%Y.%m.%d') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%Y.%m.%d')
    WHEN STR_TO_DATE(regionexpires, '%Y-%m-%d') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%Y/%m/%d') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%Y/%m/%d')
    
    WHEN STR_TO_DATE(regionexpires, '%d.%m.%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%d.%m.%Y')
    WHEN STR_TO_DATE(regionexpires, '%d-%m-%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%d-%m-%Y')
    WHEN STR_TO_DATE(regionexpires, '%d/%m/%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%d/%m/%Y')
    
    WHEN STR_TO_DATE(regionexpires, '%m.%d.%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%m.%d.%Y')
    WHEN STR_TO_DATE(regionexpires, '%m-%d-%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%m-%d-%Y')
    WHEN STR_TO_DATE(regionexpires, '%m/%d/%Y') IS NOT NULL THEN STR_TO_DATE(regionexpires, '%m/%d/%Y')
    ELSE NULL
END

    Rows matched: 10  Changed: 6  Warnings: 52

    -- Re-enable strict mode if desired
SET sql_mode = 'STRICT_TRANS_TABLES';

    -- Sample query to retrieve data from the "roster" table with formatted dates
SELECT id, name, expiry_date,regionexpires,
  CASE 
    WHEN STR_TO_DATE(regionexpires, '%Y.%m.%d') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%Y.%m.%d'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%Y-%m-%d') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%Y-%m-%d'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%Y/%m/%d') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%Y/%m/%d'), '%Y-%m-%d')
    
    WHEN STR_TO_DATE(regionexpires, '%d.%m.%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%d.%m.%Y'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%d-%m-%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%d-%m-%Y'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%d/%m/%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%d/%m/%Y'), '%Y-%m-%d')
    
    WHEN STR_TO_DATE(regionexpires, '%m.%d.%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%m.%d.%Y'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%m-%d-%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%m-%d-%Y'), '%Y-%m-%d')
    WHEN STR_TO_DATE(regionexpires, '%m/%d/%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(regionexpires, '%m/%d/%Y'), '%Y-%m-%d')
    ELSE 'Invalid date'
  END AS regionexpires_formatted
FROM roster;
    id name expiry_date regionexpires regionexpires_formatted
    1 John Doe 2022-12-31 2022-12-31 2022-12-31
    2 Jane Smith 2031-12-20 31.12.2022 2031-12-20
    3 Bob Johnson 2022-12-31 2022.12.31 2022-12-31
    4 Alice Lee 2031-12-20 31-12-2022 2031-12-20
    5 Tom Brown 2022-12-31 2022/12/31 2022-12-31
    6 Sara Green 2022-12-31 12/31/2022 2022-12-31
    7 Mike Davis null 2022-31-12 Invalid date
    8 Emily White null 31-2022-12 Invalid date
    9 Mark Black null 2022-12-32 Invalid date
    10 Lisa Grey null 2022-13-31 Invalid date

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search