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;
Jan 30 2013 12:00:00:000AM
DD/MM/YYYY or in this case
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,
If DB is SQL Server then
Read more... Read less...
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)
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
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.
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