asked on

Convert Varchar to Date

Experts -

How can i convert "Aug-12" varchar column to a datetime format?

Ex: "Aug-12" should be "mm/dd/yyyy" format after conversion.

Kindly help.

ste5an

This is not possible, but when you remove the hyphen and add a year you can convert it:

DECLARE @var VARCHAR(255) = 'Aug-12';

SELECT  @var ,
        CONVERT(DATE, REPLACE(@var, '-', ' ') + ', 2015', 107);

Open in new window

btw, don't mix data types with their possible textual representations. So the above converts your value to a DATE, you may also use DATETIME. To get a specific format, you need to convert CONVET() it or use FORMAT():

DECLARE @var VARCHAR(255) = 'Aug-12';

SELECT  @var ,
        CONVERT(DATE, REPLACE(@var, '-', ' ') + ', 2015', 107);

DECLARE @date DATE = CONVERT(DATE, REPLACE(@var, '-', ' ') + ', 2015', 107);

SELECT  @date ,
        CONVERT(VARCHAR(255), @date, 101) ,
        FORMAT(@date, 'd', 'en-US');

Open in new window

Vitor Montalvão

A date without year? You should at least provide the year so the conversion can be made.

Manju

ASKER

Apr-12 is 1st April 2012... and so on

Vitor Montalvão

What's so on? How do you provide 2nd of April?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Manju

ASKER

Victor - My Bad, the column has only the month and year in the format i gave. I dont need the date's as it wouldnt be necessary for me to run the query. Having said that, Stephan's query works like a charm.