Convert varchar to date format

Question

I want to covert varchar (50) column to date format. I used following code:

Update [dbo].[KYCStatus062013] 
Set [REGISTRATION_DATE_]= convert(datetime,[REGISTRATION_DATE_] ,103)

But there is an error that says:

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

I want this format: dd-mmm-yyyy. I do not have any option to create another table / column so "update" is the only way I can use. Any help will be highly appreciated.

Edit: my source data looks like this:

21-MAR-13 07.58.42.870146 PM  
01-APR-13 01.46.47.305114 PM  
04-MAR-13 11.44.20.421441 AM  
24-FEB-13 10.28.59.493652 AM

Edit 2: some of my source data also contains erroneous data containing only time. Example:

 12:02:24
 12:54:14
 12:45:31
 12:47:22

you are trying to update column REGISTRATION_DATE_ on the same column converted to datetime. — Roman Pekar
possible duplicate of Convert Date format into DD/MMM/YYYY format in SQL Server 2008 — Vishal Suthar
@RomanPekar that doesn't seems to be the problem, since the target column is varchar — Luis LL
possible duplicate of How to convert the system date format to dd/mm/yy in SQL Server 2008 R2? — bgs

Hiren gardhariya Hiren gardhariya · Accepted Answer · 2013-08-12T05:02:08

Try this one.

Update [dbo].[KYCStatus062013] 
Set [REGISTRATION_DATE_]= REPLACE(CONVERT(VARCHAR(11),[REGISTRATION_DATE_],106),' ' ,'-')

this will give output as dd-mmm-yyyy

if you want to update as date format then you have to modify your table.

Edit 1 =

Update [dbo].[KYCStatus062013] 
    Set [REGISTRATION_DATE_]= REPLACE(CONVERT(VARCHAR(11),convert(datetime,left([REGISTRATION_DATE_],9),103),106),' ' ,'-')

Edit 2 = Check this

http://sqlfiddle.com/#!3/d9e88/7

Edit 3 = Check this if you have only enter time

http://sqlfiddle.com/#!3/37828/12

Convert varchar to date format

3 Answers