[sql] SQL Query - Change date format in query to DD/MM/YYYY

What I'm trying to achieve is fairly straight forward, to get one date format to another;

From This: Jan 30 2013 12:00:00:000AM To This: DD/MM/YYYY or in this case 30/01/2013

However, when it's the 1st to the 9th of the month the date format is missing a zero and there is two spaces, as shown;

Jul  3 2014 12:00:00:000AM

I've hunted round for a solution but without success, the format of the date is unusual and varies depending on the day of the month however this format is generated by an internal system and can't be changed. Would I then have to pattern match in order to change the format? How would I go about doing this?

An example of part of the query is this;

SELECT
PREFIX_TableName.ColumnName1 AS Name,
PREFIX_TableName.ColumnName2 AS E-Mail,
PREFIX_TableName.ColumnName3 AS TransactionDate,
PREFIX_TableName.ColumnName4 AS OrderNumber,
...

The line to be edited is this PREFIX_TableName.ColumnName3 AS TransactionDate,

Try http://www.sql-server-helper.com/tips/date-formats.aspx. Lists all formats needed. In this case select Convert(varchar(10),CONVERT(date,YourDateColumn,106),103) change 103 to 104 id you need dd.mm.yyyy

If DB is SQL Server then

select Convert(varchar(10),CONVERT(date,YourDateColumn,106),103)

SELECT CONVERT(varchar(11),Getdate(),105)

If you have a Date (or Datetime) column, look at http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

 SELECT DATE_FORMAT(datecolumn,'%d/%m/%Y') FROM ...

Should do the job for MySQL, for SqlServer I'm sure there is an analog function. If you have a VARCHAR column, you might have at first to convert it to a date, see STR_TO_DATE for MySQL.

If I understood your question, try something like this

declare @dd varchar(50)='Jan 30 2013 12:00:00:000AM'

Select convert(varchar,(CONVERT(date,@dd,103)),103)

Update

SELECT
PREFIX_TableName.ColumnName1 AS Name,
PREFIX_TableName.ColumnName2 AS E-Mail,
convert(varchar,(CONVERT(date,PREFIX_TableName.ColumnName3,103)),103) AS TransactionDate,
PREFIX_TableName.ColumnName4 AS OrderNumber