Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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.
0
softbless
Asked:
softbless
3 Solutions
 
Dave BaldwinFixer 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
 
cyberkiwiCommented:
Or

select str_to_date(closing_date, "%d/%m/%Y") from tbl;
0
 
jhp333Commented:
Try this:

SELECT CAST(STR_TO_DATE(closing_date, "%d/%m/%Y") AS DATETIME)
0
 
softblessAuthor Commented:
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now