p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: SQL Query


Message #1 by "Mark Irvine" <mark@m...> on Tue, 5 Mar 2002 11:48:18 -0000
Rodger,

Many thanks for your reply.  You are correct that my orignal query was
insufficient.  However the reason I changed from what I orignally wrote to
what you suggested was because of the layout of the date.  I'm not sure
where you live, but I live in the UK and for some reason the date field of
my hosting company's SQL server is in the Americian format.  Which means
when I entery the 7 March 2002 is stored as the 3 July 2002.  This meant
that incorrect values were being used from the start.

I had not considered the point you made regarding the year, I had just
assumed that that situation would not arise.  Breaking the golden rule -
assume nothing!

Again many thanks for your post.

Mark

<rnedel@b...> wrote in message news:153351@s..._language...
>
> Mark:
>
> Yes, I think there is a problem with your DELETE statement.  Let's first
> make sure I understand what you want to accomplish.
>
> - You want your query to delete all records having a departure
>   date earlier than whatever the current date is.
>
> Assuming this is correct, then here's an illustration of the problem:
>
> 1.  Assume today's date is March 05.
>
> 2.  Assume your table has three records, containing departure dates:
>
>     - March 03
>     - March 17
>     - April 02
>
>    One would want this query to delete only March 03, however
>    your query would:
>
>     - Correctly delete March 03 (dep_day < curr_day)
>     - Correctly leave March 17
>     - Incorrectly delete April 02 (dep_day < curr_day)
>
> Next, I disagree with your assertion that you don't need to check the
> year.  Take a look at the following example
>
> 1.  Assume today's date is November 15 2002.
>
> 2.  Assume your table has three records, containing departure dates:
>
>     - November 03 2002
>     - January 31 2003
>     - December 12 2003
>
>     One would want this query to delete only November 03 2002, however
>     your query would:
>
>     - Correctly delete November 03 2002
>     - Incorrectly delete January 31 2003 (dep_month < curr_month)
>     - Incorrectly delete December 12 2003 (dep_day < curr_day)
>
> In my humble opinion, you should simply match the actual departure date to
> the current date rather than breaking the dates down into their sub-
> parts.  Something like the following query:
>
>   DELETE FROM lhs
>   WHERE DepartureDate < GETDATE()
>
> This will handle all situations rather than muddying the waters.  Cheers.
>
> - Roger Nedel
>
>   Nedel Software Solutions
>   rnedel@b...
>
> > Hi,
> >
> > I want to delete data from a table based on the departure date relevant
> to
> > the current date.  Just wondered if anyone could see a problem with the
> > query below:
> >
> > DELETE
> > FROM lhs
> > WHERE DATEPART(day, DepartureDate) < DATEPART(day, GETDATE())
> > OR DATEPART(month, DepartureDate) < DATEPART(month, GETDATE())
> >
> > I didn't think that there was any need to compare the year, seeing as it
> > will be run every day.
> >
> > Mark
> >
> >
>
>



  Return to Index