如何将日期转换为格式为“mm / dd / yyyy”

I'm having a sql table with date column named CREATED_TS which holds the dates in different format eg. as shown below

我有一个名为CREATED_TS的日期列的SQL表，它以不同的格式保存日期，例如。如下所示

Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Nov 16 2011 12:00AM
Feb 20 2012 12:00AM
11/29/12  8:20:52 PM

Now I want to convert these to format mm\dd\yyyy before as i am comparing the dates in WHERE clause of my SELECT query.

现在我想将这些格式转换为格式mm \ dd \ yyyy，因为我在比较SELECT查询的WHERE子句中的日期。

I tried using

我试过用

CONVERT(VARCHAR(10),CREATED_TS,101)

but got the result as,

但得到的结果是，

Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Nov 16 201
Feb 20 201
11/29/12

I need the result as eg. 02/20/2012 in order to compare.

我需要结果，例如。 02/20/2012为了比较。

Any help will be appreciated.

任何帮助将不胜感激。

4 个解决方案

#1

As your data already in varchar, you have to convert it into date first:

由于您的数据已经在varchar中，您必须先将其转换为日期：

select convert(varchar(10), cast(ts as date), 101) from <your table>

#2

Use CONVERT with the Value specifier of 101, whilst casting your data to date:

使用值为ISO的说明符为CONVERT，同时将数据转换为日期：

CONVERT(VARCHAR(10), CAST(Created_TS AS DATE), 101)

#3

Are you looking for something like this?

你在找这样的东西吗？

SELECT CASE WHEN LEFT(created_ts, 1) LIKE '[0-9]' 
            THEN CONVERT(VARCHAR(10), CONVERT(datetime, created_ts,   1), 101)
            ELSE CONVERT(VARCHAR(10), CONVERT(datetime, created_ts, 109), 101)
      END created_ts
  FROM table1

Output:

输出：

| CREATED_TS |
|------------|
| 02/20/2012 |
| 11/29/2012 |
| 02/20/2012 |
| 11/29/2012 |
| 02/20/2012 |
| 11/29/2012 |
| 11/16/2011 |
| 02/20/2012 |
| 11/29/2012 |

Here is SQLFiddle demo

这是SQLFiddle演示

#4

Use:

使用：

select convert(nvarchar(10), CREATED_TS, 101)

要么

select format(cast(CREATED_TS as date), 'MM/dd/yyyy') -- MySQL 3.23 and above

#1