SQL SERVER 2008 DATETIME CONVERSION FAILURE

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()) < 30
But this is hit your performance if you have large number of record in table

hope this help.

Sorry about that go back to the same thread,

I am getting this error below

Conversion failed when converting date and/or time from character string.

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