SQL Syntax - Date Format in MySQL

Hi Guys,

I have a field (varchar), and it contains like this :

Closing_Date
---------------
27/07/2005
17/12/2010

I wanna have "select syntax" that convert the date into this format : YYYY-MM-DD HH:MM:SS

So it will be like this : 2005-07-27 00:00:00, and 2010-12-17 00:00:00

I try this syntax, but it gave NULL : select str_to_date(closing_date, "%d-%b-%Y") from table1

Please help. Thanks before.
softblessAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Or

select str_to_date(closing_date, "%d/%m/%Y") from tbl;
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
Try this.  '%b' was expecting a text like 'Jan', '%m' is looking for a number.
select str_to_date(closing_date, "%d-%m-%Y") from table1;

Open in new window

0
 
cyberkiwiCommented:
select cast(concat(
      right(Closing_date,4),mid(Closing_Date,4,2),left(Closing_Date,2))
      as datetime)
from tbl
0
 
jhp333Connect With a Mentor Commented:
Try this:

SELECT CAST(STR_TO_DATE(closing_date, "%d/%m/%Y") AS DATETIME)
0
 
softblessAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.