SQL SERVER QUERY

Question

I have a table called ColorTable

I want to write a query to retain the latest 30 days of data in the database or table. you can use the Field call ColorTable.DateTime . The column ColorTable.DateTime field is a varchar, its a formated string. thanks

Jignesh Trivedi · Answer

hi Time in ColorTable.DateTime is not more important than try.... Delete ColorTable where Datediff(dcast(SUBSTRING ( [DateTime]  0  CHARINDEX(':'[DateTime]0)) as datetime)GETDATE()) hope this help.

David Smith · Answer

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

David Smith · Answer

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(VARCHARColor.CreatedDateTime 102)'.''/') +' '+ CONVERT(VARCHAR Color.CreatedDateTime 108)

Jignesh Trivedi · Answer

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

Senthilkumar · Answer

You can use the following code. DateTime dt; dt = DateTime.Now; Response.Write(String.Format({0:s} dt)); 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.

David Smith · Answer

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

Jignesh Trivedi · Answer

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(dCAST([DateTime] as Datetime)GETDATE()) hope this help.

Senthilkumar · Answer

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.

David Smith · Answer

Thanks sorry about that. actually i need to delete records from thats

Senthilkumar · Answer

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.