Solved

Varchar  to date format sql server

Posted on 2010-08-23
11
483 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:rpk2606
11 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33507069
select convert(varchar, convert(datetime, columnname), 101) from tablename
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33507074
update tablename set columname = convert(varchar, convert(datetime, columnname), 101)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507076
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507085
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507091
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 33507190
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
 

Author Comment

by:rpk2606
ID: 33507233
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507247
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
 
LVL 9

Accepted Solution

by:
sas13 earned 500 total points
ID: 33508132
declare @dt varchar(10)
select @dt = '2008-06-16'

set dateformat ymd -- set sequence

select CONVERT(varchar(10), CAST(@dt as datetime), 101)
0
 
LVL 7

Expert Comment

by:alphaau
ID: 33508258
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33510632
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now