Varchar to date format sql server

Hi, I have a varchar column which has dates of format 2008-06-16,2010-07-12,2009-12-12.
 I would like to convert this into mm/dd/yyyy format.

Please help me.
rpk2606Asked:
Who is Participating?
 
sas13Connect With a Mentor Commented:
declare @dt varchar(10)
select @dt = '2008-06-16'

set dateformat ymd -- set sequence

select CONVERT(varchar(10), CAST(@dt as datetime), 101)
0
 
mcv22Commented:
select convert(varchar, convert(datetime, columnname), 101) from tablename
0
 
mcv22Commented:
update tablename set columname = convert(varchar, convert(datetime, columnname), 101)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
select convert(varchar,convert(datetime,replace(col,'-','')),103)
from tbl
where len(col)=10

or

update tbl
set col = convert(varchar,convert(datetime,replace(col,'-','')),103)
where len(col)=10
0
 
cyberkiwiCommented:
I had my language set to British, which will not accept 2008-06-16 as a date unless the dashes are stripped with replace.
0
 
cyberkiwiCommented:
And it should have been 101 for mm/dd/yyyy not 103 for dd/mm/yyyy - per mcv22's solutions.
The len(col) test avoids errors, you don't need them if your data is controlled to be perfect.

select convert(varchar,convert(datetime,replace(col,'-','')),101)
from tbl
where len(col)=10

or

update tbl
set col = convert(varchar,convert(datetime,replace(col,'-','')),101)
where len(col)=10
0
 
RiteshShahCommented:
apart from 101 and 103, there are few more different date and time format available in SQL Server, have a look at below link to know other types of date and time format.

http://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html
0
 
rpk2606Author Commented:
Hi, I ma getting the following error.

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
0
 
cyberkiwiCommented:
Hence the test for len = 10.  I suspected bad data

select convert(varchar,convert(datetime,replace(col,'-','')),101)
from tbl
where len(col)=10 and isdate(convert(datetime,replace(col,'-',''))) = 1

or

update tbl
set col = convert(varchar,convert(datetime,replace(col,'-','')),101)
where len(col)=10 and isdate(convert(datetime,replace(col,'-',''))) = 1
0
 
Alpha AuCommented:
do you have any invalid data in the column? you might need to handle them seperately.
create table #temp(
txt_data varchar(20)
)

insert into #temp values ('2008-06-16')
insert into #temp values ('2010-07-12')
insert into #temp values ('2009-12-12')
insert into #temp values ('9999-99-99')--invalid date

--show the invalid data
select * from #temp where isdate(txt_data) = 0

--perform the update
update #temp 
	set txt_data = convert(varchar,convert(datetime,txt_data),101)
where isdate(txt_data) = 1

--show the updated data
select * from #temp

Open in new window

0
 
cyberkiwiCommented:
Hi rpk2606

Out of curiosity, did http:#a33507247 actually fail?
If "set dateformat ymd" works, then all the data must be in yyyy-mm-dd, which means collapsing the dashes to yyyymmdd becomes the never-fail format.

Or do your have data in yy-mm-dd format?

Just want to clear up my understanding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.