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, Aug 12 '13 at 05:00
possible duplicate of [Convert Date format into DD/MMM/YYYY format in SQL Server 2008](http://stackoverflow.com/questions/17205441/convert-date-format-into-dd-mmm-yyyy-format-in-sql-server-2008) — Vishal Suthar, Aug 12 '13 at 05:02
@RomanPekar that doesn't seems to be the problem, since the target column is varchar — Luis LL, Aug 12 '13 at 05:04
possible duplicate of [How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?](http://stackoverflow.com/questions/15017300/how-to-convert-the-system-date-format-to-dd-mm-yy-in-sql-server-2008-r2) — bgs, Aug 12 '13 at 05:06
@astander my data looks like this: 01-APR-13 01.46.47.305114 PM 04-MAR-13 11.44.20.421441 AM 24-FEB-13 10.28.59.493652 AM — Sadat Mainuddin, Aug 12 '13 at 06:45

Hiren gardhariya · Accepted Answer · 2013-08-12T10:05:50.160

2

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

edited Aug 12 '13 at 10:05

answered Aug 12 '13 at 05:02

Hiren gardhariya

1,247
10
29

I have tried this one but the result shows something like this: 21-MAR-13-0 – Sadat Mainuddin Aug 12 '13 at 07:27
now it shows following error: Conversion failed when converting date and/or time from character string. It runs the query for some time though. – Sadat Mainuddin Aug 12 '13 at 07:57
@SadatMainuddin Check Edit 2 in which i give you demo – Hiren gardhariya Aug 12 '13 at 08:38
thank you for your reply. Still I can not use it. Same error. However I have changed the query to following and this works perfectly for me. Thanks for your heads up though. Update [dbo].[KYCStatus062013] Set [REGISTRATION_DATE_]= REPLACE(CONVERT(VARCHAR(50),(left ([REGISTRATION_DATE_],9)),106),' ' ,'-') – Sadat Mainuddin Aug 12 '13 at 09:33
some of my data only contain time which is creating problem with your code. Example: 12:02:24. Hence the above code. – Sadat Mainuddin Aug 12 '13 at 09:40
However just discovered that this will not change my varchar to date. :( – Sadat Mainuddin Aug 12 '13 at 09:53
1

@SadatMainuddin Please check Edit 3 for your query. – Hiren gardhariya Aug 12 '13 at 10:08
this does it. Thank you :) – Sadat Mainuddin Aug 12 '13 at 10:18
Sorry to interrupt again. but another question. if the date format changes to m/dd/yyyy hh:mm:ss (ex: 4/17/2013 0:00:00) then what can be done? since the date is not fixed to 9 characters. – Sadat Mainuddin Aug 12 '13 at 12:59
for this refer this question [CAST and CONVERT (Transact-SQL) ](http://msdn.microsoft.com/en-us/library/ms187928.aspx) – Hiren gardhariya Aug 12 '13 at 13:08

score 1 · Answer 2 · answered Aug 12 '13 at 05:04

The error suggests that one of the values in your table does not match the 103 format and cannot be converted. You can use the ISDATE function to isolate the offending row. Ultimately the error means your table has bad data, which leads to my main concern. Why don't you use a datetime or date data type and use a conversion style when selecting the data out or even changing the presentation at the application layer? This will prevent issues like the one you have described from occurring.

I strongly recommend that you change the data type of the column to more accurately represent the data being stored.

score 0 · Answer 3 · answered Aug 22 '13 at 07:04

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types.

to_date(text, text)
to_date('05 Dec 2000', 'DD Mon YYYY')

for further details here click

http://www.postgresql.org/docs/8.1/static/functions-formatting.html

Convert varchar to date format

3 Answers3