CODEWITHSUNDEEP
mysqlstringdate
FRECEENA FRANCIS
FRECEENA FRANCIS

Reputation: 181

Convert a string to date format and retrieve the week number

I have date of the format 15-Nov-20. I want to convert it into date format of the MySQL table (yyyy-mm-dd) using query before inserting the data into the table and want to get the week and year separately of that date. I have tried the following query:

SELECT  STR_TO_DATE('15-Nov-20', '%Y-%m-%d') FROM table

But this returns NULL.

Upvotes: 0

Views: 1225

Answers (2)

Vijay Joshi
Vijay Joshi

Reputation: 959

You can also use PHP's DateTime class to get week and year numbers.

$parsedDate = new DateTime('15-Nov-20');
$week = $parsedDate->format("W");
echo 'Weeknumber:' . $week;
echo '<br>';
$year = $parsedDate->format("Y");
echo 'Year:' . $year;

This will give you:

Weeknumber:46
Year:2020

Upvotes: 0

Nick
Nick

Reputation: 147166

Your format string is incorrect (see the manual). For your sample data it should be %d-%M-%y e.g.

SELECT  STR_TO_DATE('15-Nov-20', '%d-%M-%y')

Output:

2020-11-15

To then get the year and week, you can use DATE_FORMAT with one of %U, %u, %V or %v dependent on your needs (see the manual for their definitions) e.g.

SELECT DATE_FORMAT(STR_TO_DATE('15-Nov-20', '%d-%M-%y'), '%Y-%U')

Output

2020-46

Note that if you use %V or %v for the week number, you must also use %X or %x for the year e.g.

SELECT DATE_FORMAT(STR_TO_DATE('15-Nov-20', '%d-%M-%y'), '%X-%V')

Output

2020-46

Demo on dbfiddle

Upvotes: 1

Related Questions