Simple delete query help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple delete query help?

Author

Topic

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-07-30 : 14:05:53

Hi,

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-30 : 14:08:43

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

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-07-30 : 15:57:25

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 16:12:21

quote:
Originally posted by mitin

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

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-30 : 22:41:51

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources