CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
RobertKing
RobertKing

Reputation: 1911

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.

Upvotes: 33

Views: 375099

Answers (6)

user23889931
user23889931

Reputation: 1

(Date today) ddmmyyyy.

ddmmyyyy "Answer the receiver rendered in French format mm/dd/yyyy. Note that the name here is slightly misleading -- the month and day numbers show leading zeros, so that for example February 1 1996 is 01/02/96"

^ self printFormat: #(1 2 3 $/ 1 1 2)

Upvotes: -1

Sohini
Sohini

Reputation: 1

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]: https://i.sstatic.net/zCJFc.jpg

Upvotes: 0

Mohammad Anini
Mohammad Anini

Reputation: 5220

Use:

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

or

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

Upvotes: 2

roman
roman

Reputation: 117337

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>

Upvotes: 58

Mathew Thompson
Mathew Thompson

Reputation: 56429

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

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

Upvotes: 28

peterm
peterm

Reputation: 92785

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

Upvotes: 5

Related Questions