j-M-Y h:i a to mm-dd-yyyy select query in mysql-CodePudding

Im a bit trap in this date format.

I have a date format in my database "j-M-Y h:i a" = "10/15/2020 Thu 09:29:35 am"

i am trying to convert this date to mm-dd-yyyy format so i can get the year and make a trap sql code to select only 2022 dates in my database. LIKE THIS QUERY

SELECT * FROM document WHERE date_received >= 2022

This code not work for me

SELECT * FROM document WHERE YEAR(date_received) >= 2022

i also tried other conversion query but i only getting "null" or invalid syntax

CodePudding user response：

First, you have to cast your field as date. First trim that field. Then using date_format as '%Y", you can extract four digit year from it. If you want two digit year, you have to use '%y'.

SELECT * FROM document WHERE DATE_FORMAT(CAST(TRIM(date_received) AS DATE),'%Y') >= '2022';

CodePudding user response：

If you are using varchar field for datetime values you have to convert string value to datetime using STR_TO_DATE function after that YEAR function will work becase YEAR function take date field or datetime field as input:

In your case like this:

SELECT * 
FROM document 
WHERE YEAR(STR_TO_DATE("10/15/2020 Thu 09:29:35 am", '%m/%d/%Y')) >= 2022

SELECT * 
FROM document 
WHERE YEAR(STR_TO_DATE(date_received, '%m/%d/%Y')) >= 2022

CodePudding user response：

If your column data type is string "17-Aug-2020" in that case you need to convert string to date and then cast in expected date format then using %Y you can get year from your string.

SELECT * FROM document m WHERE DATE_FORMAT(CAST(TRIM(STR_TO_DATE(date_received  ,'%d-%M-%Y')) AS DATE),'%Y') >= '2022';

try this example also

SELECT DATE_FORMAT(CAST(TRIM(STR_TO_DATE('May-01-2022','%M-%d-%Y')) AS DATE),'%Y')