SQL Query - Change date format in query to DD MM YYYY

Question

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

User · Answer

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

User · Answer

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

User · Answer

If DB is SQL Server then  select Convert varchar 10  CONVERT date YourDateColumn 106  103

User · Answer

SELECT CONVERT varchar 11  Getdate   105

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008