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.
This question is related to
sql
sql-server
sql-server-2008
tsql
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
Source: Stackoverflow.com