I have inherited a SQL Server 2005 database without warning at work and would like to know how to delete rows from a table that contain certain data in the "date" column of the table. The thing is that the column does not only contain the date, it also contains the time, so data in cells in this column will be like this example:
2012-05-30 10:34:05
I only want to delete based on the date, not the time aswell, the time does not matter.
So i need to do something like:
delete * from exampletable where date contains "2012-05-30"
Can someone please give me an example of the query that I need and that will work? its pretty desperate times here. many thanks!!
Here is the basic gist. If you need more help, just let us know:
delete exampletable
where DateColumn >= '2012-05-30' -- Day you want to delete
AND DateColumn < '2012-05-31' -- One day greater than the day you want to delete
Thanks for the reply lamprey, but the thing is I do not want to delete the whole table (it looks like this is what your example query would do). I only want to delete the rows in the table that have a certain date in the date column, also, I hope I am wrong, but I do not think that your example query would get around the issue of the date not being the only data in the cell in the "Date" column. I get the impression that if it was the only thing in there and there wasn't the time in there too then it would work.... can you please confirm? I know I could easily be wrong!
i need a query that will delete all rows in the table (not the table itself) that have a specific date in the date column, and which takes into account that the date is not the only data in the cells in the date column.
Thanks for the reply lamprey, but the thing is I do not want to delete the whole table (it looks like this is what your example query would do). I only want to delete the rows in the table that have a certain date in the date column, also, I hope I am wrong, but I do not think that your example query would get around the issue of the date not being the only data in the cell in the "Date" column. I get the impression that if it was the only thing in there and there wasn't the time in there too then it would work.... can you please confirm? I know I could easily be wrong!
i need a query that will delete all rows in the table (not the table itself) that have a specific date in the date column, and which takes into account that the date is not the only data in the cells in the date column.
Thanks again! i appreciate your help.
The given suggestion does exactly what you're asking
first fire a select and see data it gives you
select datecolumn,*
from exampletable
where DateColumn >= '2012-05-30' -- Day you want to delete
AND DateColumn < '2012-05-31'
see date values its returning for rows and then you'll understand how it will fetch you range of dates without considering time part.
then once happy make it into delete query
also see below to understand how dates are stored internally in sql server which is why above type of query retrieves date interval range
Hi Mitin, I want you to take this in the spirit it is intended:
STOP!
Seriously, if you do not understand enough to know what Lamprey's code will do then you are in no way qualified to "look after" a database. You need to take it up with your boss and get some training or at least take some time to study. You can get yourself in enough trouble just doing SELECT let alone DELETE when you don't understand what you're doing. At very least learn how to do a backup & restore so you can practice on a copy of the database. I mean this kindly but seriously - do consider this if you are expected to do anything with the database and you care about the data it contains.
Simple delete query help?
Posted - 07/30/2012 : 14:05:53
