convert text date to datetime
Hello, I have a varcahr(12) column filled with month/year values like this:
11/91
11/91
11/91
01/94
12/88
06/04
06/95
10/11
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.
11/91
11/91
11/91
01/94
12/88
06/04
06/95
10/11
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Yes, please use datetime
ASKER
Awesome!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The one you have mm/yy is not an existing format that convert would understand.
http://technet.microsoft.com/en-us/library/ms187928.aspx
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)