Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to convert date to a format `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

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.

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.

Any help will be appreciated.

like image 323
RobertKing Avatar asked Sep 02 '13 08:09

RobertKing


People also ask

How do I format a date in dd mm yyyy in Excel?

First select your cells containing dates and right click of mouse and select Format Cells. In Number Tab, select Custom then type 'dd-mmm-yyyy' in Type text box, then click okay. It will format your selected dates.


3 Answers

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

select convert(varchar(10), cast(ts as date), 101) from <your table>
like image 67
Roman Pekar Avatar answered Oct 20 '22 23:10

Roman Pekar


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

CONVERT(VARCHAR(10), CAST(Created_TS AS DATE), 101)
like image 36
mattytommo Avatar answered Oct 20 '22 22:10

mattytommo


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

like image 5
peterm Avatar answered Oct 21 '22 00:10

peterm