Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Unanswered: Need Help in Query using date

    Hi!

    I have a DB with this fields:
    MID - primary key
    EVENT - varchar(2)
    EVENT_DATE datetime (format mm/dd/yyyy)

    I need to design a query that will pickup records from DB with EVENT = '02' and EVENT_DATE equal or earlier than 18 days from the date the query was executed.

    In this example, the query should display these 2 records if ran today:
    MID EVENT EVENT_DATE
    01 02 12/25/2005
    02 02 12/20/2005

    But not this:

    MID EVENT EVENT_DATE
    03 03 12/25/2005
    04 02 01/01/2006

    Any help will be greatly appreciated.

  2. #2
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    I don't know what your table is called, let's say it's called EVENTS. So your query would be:

    Code:
    select * from EVENTS where event_date <= dateadd(dd,-18,getdate()) and EVENT = '02'

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Or even
    Code:
    SELECT * 
    FROM Events 
    WHERE Event_Date <= CONVERT(varchar(10), GETDATE() - 18, 101)
             AND Event = '02'
    You can apply the date arithmetic directly to the Getdate...just be sure to account for the Time part of getdate's return if it might cause you grief (which it likely would using comparing against a datetime column in your table).

    Or better yet, put your target date in a local variable so you don't have to have a calculation/conversion in the select, as in:
    Code:
    DECLARE @TargetDate varchar(10)
    SET @TargetDate = CONVERT(varchar(10), GETDATE() - 18, 101)
    
    SELECT * 
    FROM Events 
    WHERE Event_Date <= @TargetDate
             AND Event = '02'
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you converting to varchar(10) when Event_Date is a datetime datatype? Also, it's better to use dateadd() rather than actually adding an integer value to a datetime value (which occasionally gives incorrect results).
    Code:
    SELECT	* 
    FROM	Events 
    WHERE	Event_Date <= dateadd(d, -18, GetDate())
            AND Event = '02'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by blindman
    Why are you converting to varchar(10) when Event_Date is a datetime datatype? Also, it's better to use dateadd() rather than actually adding an integer value to a datetime value (which occasionally gives incorrect results).
    Because I said so, that's why!

    Seriously, though, I have been under the assumption that the conversion to varchar(10) is the easiest way to remove the time from the datetime data type when doing comparisons (and the SET utilizes the implicit conversion between varchar dates and datetime). For example, if you do it your way, you would only get rows with a date greater than or equal to 18 days ago, but nothing ON that 18th day earlier than the current system time, no?

    Even if you do the dateadd version, this would still be the case. I haven't seen any problems with the date arithmetic, but since you have much wider experience (both in RL and in text perusal formats) I will defer to your position on that point. Although now I am concerned about when my existing code might break *LOL*

    In any case, I do agree that the dateadd is made for such purposes, an anyone that knows me, knows that I would never bastardize the intended use of any programming language just to achieve my dastardly ends :wink,wink,nudge,nudge:

    Just providing alternatives. And I still hold to my varchar guns (or at least the intent behind it) - if there is an easier, handy way to disregard time, I'm all about learnin' . I know that you could do it with dateadd(hh.mm.ss, datepart(hh...mm..ss), yada,yada,yada... from the current time, but that just is uglier.
    Last edited by TallCowboy0614; 01-11-06 at 17:51.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by TallCowboy0614
    For example, if you do it your way, you would only get rows with a date greater than or equal to 18 days ago, but nothing ON that 18th day earlier than the current system time, no?
    Yup. Using the dateadd method returns a value equivalent to the same time of day. However...the example he gave used whole dates. An alternate method is to use the datediff function instead, which deals in units of whole dates.

    The problem with any varchar conversion back and forth from datetime is that buried within it is a loop, plus formatting logic, and for these reasons is much more ineffecient than dateadd and datediff which do simple arithmetic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    stored procedure

    I'm curious what the 101 represents in: CONVERT(varchar(10), GETDATE() - 18, 101).

    Also, would making it 19 verses 18 with the dateadd function work with the getdate() to account for the time?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    101 is the particular format. For details see SQL SERVER BOOKS ONLINE -> CONTENT - > TRANSACT SQL REFERENCE -> CAST AND CONVERT.

    You second question I am not sure I understand. 18 is thwe number of units, in this case days, we are subtracting.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Making it 19 instead of 18 would just create a different problem, as the date range matched would extend into the previous day. DateAdd() shifts a datetime value, retaining the time of day. DateDiff() calculates whole date differences, ignoring the time of day. A subtle difference which makes each one uniquely applicable to different tasks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •