31

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.

Community
  • 1
  • 1
RobertKing
  • 1,853
  • 8
  • 30
  • 54
  • 4
    Your problem is because you are storing dates as varchars! There is no good reason to do this ever. Store dates as dates! [Please give this article a read](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) – GarethD Sep 02 '13 at 08:42
  • @GarethD He should actually store them as datetime – Mohammad Anini Nov 10 '17 at 08:42
  • 1
    Surprised no one linked the Date and Time Styles documentation chart https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles – undrline - Reinstate Monica Aug 22 '18 at 02:57

5 Answers5

53

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>
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • 9
    This is correct with regard to converting to date first, but `111` is for `yyyy/mm/dd`, the OP already has the right style (101). – GarethD Sep 02 '13 at 09:01
  • got the required output using your suggestion but needed to convert the date i wanted to compare to this format as well.. – RobertKing Sep 02 '13 at 09:41
  • convert(varchar(10), , 111). But, honestly, it's worth to think about storing and comparing your dates as dates – Roman Pekar Sep 02 '13 at 09:44
  • 1
    @GarethD wow after 4 years I've found your comment. Thanks for pointing out, updated – Roman Pekar Oct 23 '17 at 20:19
27

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

CONVERT(VARCHAR(10), CAST(Created_TS AS DATE), 101)
Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
5

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

peterm
  • 91,357
  • 15
  • 148
  • 157
2

Use:

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

or

select format(cast(CREATED_TS as date), 'MM/dd/yyyy') -- MySQL 3.23 and above
Mohammad Anini
  • 5,073
  • 4
  • 35
  • 46
0

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.stack.imgur.com/zCJFc.jpg
Sohini
  • 1
  • 1