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.

5 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> 

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

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

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

Use:

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

or

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

Date in SQL Server is by default in YYYY-MM-DD format. If you want to convert any column in SQL Server be it Date of Birth or Shipping Date, Manufacturing Date etc....to dd/mm/yyy format you can use the following method.

Firstly understand that formatting any column is applicable only in portraying of the output to the user and will not hold any value to how the data is stored. Which means that the data will be stored in the server in its original format but when the output is displayed you can control how it will be displayed.

To convert a column in dd/mm/yyyy format you can use the below:

Imagine there is a table called Employee_Details as below:

 ID Dept Salary DOB 1 Akash HR 30000 1989-02-25 2 Milind Finance 45000 1975-12-15 3 Ananya Admin 25000 1988-08-13 4 Girish Finance 58000 1989-05-19 5 Ujwala HR 28000 1997-04-26 6 Mahesh Sales 60000 1982-11-15 7 Supriya Marketimg 80000 1979-09-30 8 Nidhi Admin 25789 1980-03-02 9 Jai Sales 70000 1999-07-06 10 Aditya Sales 56000 1980-02-28 

I will covert the DOB column to display the results in an alias column in dd/mm/yyyy format

Select ID,Name,Department,Salary,Convert(nvarchar,DOB,103) as DateofBirth from EMPLOYEE_DETAILS ``[enter image description here][1] Hope this will help [1]: