Solved

Varchar  to date format sql server

Posted on 2010-08-23
11
485 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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 …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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