0

Can the MYSQL function STR-TO-DATE convert a string in this format 99-MMM-9999 (ex., '21-Sep-2014' to a date format? And what if that field is defined as 12 chars instead of the specific 11?

I tried this and it got NULLS.

DaveL
  • 101
  • 1
  • 7

2 Answers2

0

You can, just setting proper format mask:

SELECT STR_TO_DATE('21-Sep-2014','%d-%M-%Y');

If you have 12 chars, set proper mask, sample:

SELECT STR_TO_DATE(' 21-Sep-2014',' %d-%M-%Y');
dani herrera
  • 48,760
  • 8
  • 117
  • 177
0
    SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
    FROM    yourtable

You can also handle these date strings in WHERE clauses. For example

    SELECT whatever
  FROM yourtable
 WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAYS

You can handle all kinds of date / time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you can put into the second parameter to STR_TO_DATE()

reference :- how to convert a string to date in mysql?

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Community
  • 1
  • 1
Khurram Ali
  • 1,659
  • 4
  • 20
  • 37
  • Thanks - I found my key mistake was that I should have been using '%b' to tell it was an abbreviated month., ex, Jan, Feb, etc, – DaveL Sep 21 '14 at 12:08
  • @DaveL Is the answer Useful for You if yes then Please Accept the answer – Khurram Ali Sep 21 '14 at 12:11