SQL SERVER 2008 DATETIME CONVERSION FAILURE

Question

this is giving me an error below , failed conversion, keep in mind the format thats in the database is 'YYYYMMDD:hhmmss' try this value '20111112:000000' can someone assist me

DELETE
FROM Color
WHERE REPLACE(CONVERT(VARCHAR,Color.CreatedDateTime, 102),'.','/') +' '+ CONVERT(VARCHAR, Color.CreatedDateTime, 108) < (GETDATE()-30)

Jignesh Trivedi · Answer

Hi, Insted of creating your own function you can also use other way try.. Delete ColorTable where Datediff(d,CONVERT(datetime, CONVERT(varchar(8), [Datetime]) + ' ' + STUFF(STUFF ( Right([Datetime], 6), 3, 0, ':'), 6, 0, ':')),GETDATE()) < 30

Jignesh Trivedi · Answer

hi, create function in sql that convert ur date to sql Equivalent format. Example. CREATE FUNCTION [dbo].[GetMyFormatDate] ( @Original VARCHAR(max) ) RETURNS Datetime AS BEGIN DECLARE @Date varchar(50) DECLARE @Time varchar(10) set @Date = SUBSTRING ( @Original , 0 , CHARINDEX(':',@Original,0)) set @Time = SUBSTRING ( @Original , CHARINDEX(':',@Original,0)+1,LEN(@Original)) set @Time = SUBSTRING ( @Time , 0,3) + ':' + SUBSTRING ( @Time , 3,2) + ':' + SUBSTRING ( @Time , 5,2) set @Date = @Date + ' ' + @Time RETURN cast(@Date as datetime) END And Try... Delete ColorTable where Datediff(d,dbo.GetMyFormatDate([Datetime]),GETDATE()) But this is hit your performance if you have large number of record in table hope this help.

Shen Hengbin · Answer

I think the datatype of your datacolumn in your database does not DateTime. And you can't convert '20111112:000000' to DateTime directly so you get this error. you can try select CONVERT(VARCHAR, '20111112:000000', 111) and you will find it has no effect.

David Smith · Answer

Sorry about that go back to the same thread,

David Smith · Answer

I am getting this error below Conversion failed when converting date and/or time from character string.

Senthilkumar · Answer

Hi, Why are you creating duplicate threads for the same questions. This one is related to your previous thread and previous thread is related to another thread. Try to continue the same thread for the same questions. I would strong suggest you to read the basics of the SQL server. The syntax of the delete is like the following. Lets consider a table name is Sample. DELETE FROM Sample -- This will delete all the records in the sample table. DELETE FROM Sample WHERE id=5 -- This will delete the records which have id values are 5. If there is no id having value 5 then it will not delete. DELETE FROM Sample WHERE createdDate = CONVERT(VARCHAR,Color.CreatedDateTime, 111) -- This will delete the records having matching date. More over, the datetime conversion you are doing may not match with the date time you are having in the database column. check it and do it....

SQL SERVER 2008 DATETIME CONVERSION FAILURE

Answers (6)