convert text date to datetime

Hello, I have a varcahr(12) column filled with month/year values like this:


How do I convert that string date to a real datetime?

I tried

select convert(date, '10/09')

But got this error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
select CONVERT(datetime,'01/'+'10/09',3)

Open in new window

You are missing the format in which the current one is.
The one you have mm/yy is not an existing format that convert would understand.

One way is to convert by update converting mm/yy to dd/mm/yy by pretending 01 presuming it references the begining of the period and then reference type 3
Convert (datetime,date,3)
gogetsomeAuthor Commented:
I'm going to use the datetime value for an order by would resulting format lend to this requirement or is there another type to use?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HuaMin ChenProblem resolverCommented:
Yes, please use datetime
gogetsomeAuthor Commented:

When I take out my test values and use the column name I get an error. Most likely due to some values being null.

Is there a way to change the statement below to only convert the ones that are not null

select  CONVERT(datetime,'01/'+ pubdate ,3) as pubdate, title from product
order by pubdate
Select isnull(date,'',convert(datetime,'01/'+date,3)) as pubdate,title from product order by isnull(date,'',convert(datetime,'01/'+date,3))

If you need the data in a specific format, dd/mm/yyyy you would need to do a double convert.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.