Solved

Varchar  to date format sql server

Posted on 2010-08-23
11
484 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

896 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