conversion failed when converting date and/or time from character string

Question

Hi,

I have a date field with datatype as Char(24), now I want to convert it into a char(8) 

Datefield                                              
------------
2012-07-18 16:47:27.223 


required Output:

Datefield
---------------
20120718

The syntax I'm using CONVERT(varchar(8), CAST(datefield as Datetime), 112), but this is giving the error as "conversion failed when converting date and/or time from character string"

can anyone let me know the correct syntax

Answer 1

SELECT CONVERT(CHAR(10),'2012-07-18 16:47:27.223',112)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

Hi

possibly this will work

SELECT REPLACE(CONVERT(CHAR(10),'2012-07-18 16:47:27.223',112),'-','')

Answer 3

Try

CONVERT(varchar(8), CONVERT(datetime,datefield,120), 112)

---

Many Thanks & Best Regards, Hua Min

Answer 4

SELECT CONVERT(varchar(8), CAST(datefield as Datetime), 112)

This works perfectly fine, you may have some invalid dates as the datatype is Char(24), please try to find them using this query

SELECT datefield from yourtable
where isdate(datefield )=0

Regards
Satheesh

Answer 5

Hi,

Select CONVERT(varchar(8), CAST('2012-07-18 16:47:27.223' as Datetime), 112)

Thanks

I've executed the below code ,executed successfully

---

Ahsan Kabir