CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Soham M
Soham M

Reputation: 25

How do I convert yymmdd to dd/mm/yy format?

I am using SQL Server 2008 and have the column MyDate that contains the date as 20141208. Its type is user-defined (UTypeDate:varchar(8)), and I want to convert it to 08/12/2014.

For example, given

MyDate (UTypeDate:varchar(8))
20141208
20141218
20141204
20141216

what I want is to write the query that will give me output as:

MyNewDate
08/12/2014
18/12/2014
04/12/2014
16/12/2014

How should I write the query for this?

Upvotes: 0

Views: 5486

Answers (2)

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

Try this

SELECT CONVERT(VARCHAR(10), CAST('20141208 ' AS DATE), 103)

RESULT

enter image description here

I think you need an extra column on your desired format. Then use the following query.

SELECT VARCHARDATE,
CONVERT(VARCHAR(10), CAST(VARCHARDATE AS DATE), 103) NEWDATE
FROM YourTable

which forms the below result

enter image description here

You can apply your logic con NEWDATE columns as per your requirements.

Let me know for any changes

Check the sqlfIDDLE http://sqlfiddle.com/#!3/af366/1 (Click RUNSQL button when site not loaded properly)

Upvotes: 2

Nasir Mahmood
Nasir Mahmood

Reputation: 1505

try this

SELECT CONVERT(VARCHAR(10), CAST(MyDate AS DATE), 103) FROM TableName

For visual prrof

Table Design

Table Design

Data

Data

Result

Result

Upvotes: 0

Related Questions