SQL SERVER QUERY

hi,

Time in ColorTable.DateTime is not more important than
try....

Delete ColorTable where Datediff(d,cast(SUBSTRING ( [DateTime] , 0 , CHARINDEX(':',[DateTime],0)) as datetime),GETDATE()) < 30

hope this help.

my format is below

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'


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

Hi,

I think no.

if your format YYYYMMDD hh:mm:ss then it is possible to conver in SQL server.

You can use the following code.

The output will be like the following.

2012-03-13T10:48:01 

Still you want the output of the  YYYYMMDD:hhmmss  format then

You need to replace the result string like the following.

string str = String.Format("{0:s}", dt).Replace("-", "").Replace(":","").Replace("T", ":");

Mark it as "Accepted Answer" if you found useful.

The format I am using  is YYYYMMDD:hhmmss so can i convert this to a date time in SERVER MANAGEMENT STUDIO

hi,

if your column ColorTable.DateTime is a varchar, then u also consider how you store data in this filed, If you enter this data in UTC (yyyy-MM-dd) format then it easyly convert in to datatime format.

try...

Delete ColorTable where Datediff(d,CAST([DateTime] as Datetime),GETDATE()) < 30

hope this help.

You can delete using the following query.

DELETE FROM  ColorTable WHERE id NOT IN(SELECT id FROM  ColorTable  WHERE  CONVERT(VARCHAR,  ColorTable.DateTime , 107) > (GETDATE()-30))

I have used key column to delete the record. Use your primary key column to replace the id column in the above query.

If it is useful and answered your question then mark it as "Accepted Answer".

Thanks, sorry about that. actually i need to delete records from thats < than the latest 30 days. how to set it up for the deletion of records thats less than 30 days. We want to keep the latest 30 days of records in the database. We are cleaning up old records, can you assist me with the delete.

Please use this query. I checked and its working fine.


SELECT * FROM ColorTable WHERE  CONVERT(VARCHAR, ColorTable.DateTime , 107) > (GETDATE()-30)

Here i want to make a note of you mentioned the column name is DateTime. But it shouldn't be.
I suggest you to use the same data type. Because it will make unnecessary type casting overload to the server.

If this post is useful then do not forgot to mark it as "Accepted Answer".