SQL查询 - 将查询中的日期格式更改为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

来自：2013年1月30日12：00：00：000AM对此：DD / MM / YYYY或在这种情况下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;

但是，当它是月份的第1天到第9天时，日期格式缺少零，并且有两个空格，如图所示;

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,

要编辑的行是此PREFIX_TableName.ColumnName3 AS TransactionDate，

5 个解决方案

#1

If DB is SQL Server then

如果DB是SQL Server那么

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

#2

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

#3

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

如果您有日期（或日期时间）列，请查看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.

应该为MySQL做的工作，对于SqlServer，我确信有一个模拟功能。如果您有VARCHAR列，则可能首先将其转换为日期，请参阅STR_TO_DATE for MySQL。

#4

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

#5

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

请尝试http://www.sql-server-helper.com/tips/date-formats.aspx。列出所需的所有格式。在这种情况下，选择Convert（varchar（10），CONVERT（date，YourDateColumn，106），103）将103改为104，你需要dd.mm.yyyy

#1