Solved

Varchar  to date format sql server

Posted on 2010-08-23
11
492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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:Alpha Au
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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