SQL Server Performance

Noob question regarding date functionality in the "WHERE" clause

Discussion in 'General Developer Questions' started by tenderfoot, Oct 11, 2007.

  1. tenderfoot New Member

    Hiya guys,
    I am trying to pull out records as entered into the database. But I only want to pull out records that have a date in the date column that is today's date or further into the future. Dates before today's date I do not want to display. The issue I have is that I have created a WHERE clause which does this but does not display records with today's date, only future dates beyond today's date.

    This is my code so far:



    IF @EventCategory = 'All Events' BEGIN
    SET LANGUAGE british
    Declare @Today DATETIME
    SET @Today = GETDATE()

    SELECT
    ceav_title.Value AS Title,
    CAST(ceav_startdate.Value as datetime) AS StartDate,

    FROM ContentElement ce
    LEFT JOIN ContentElementAttributeValue AS ceav_title ON ceav_title.AttributeID = 17 AND ceav_title.ContentElementID = ce.ID
    LEFT JOIN ContentElementAttributeValue AS ceav_startdate ON ceav_startdate.AttributeID = 24 AND ceav_startdate.ContentElementID = ce.ID

    WHERE TypeID =1 And CAST(ceav_startdate.Value as datetime) >= @Today
    ORDER BY StartDate ASC

    END ELSE BEGIN
    PRINT 'An Error has occurred!'
    END

    Can someone help? What have I missed?
    Thanks in advance!
  2. FrankKalis Moderator

  3. Madhivanan Moderator

    CAST(ceav_startdate.Value as datetime) >= dateadd(day,datediff(day,0,@Today),0)
  4. tenderfoot New Member

    Aaah thank you! So you need to use a combination of dateadd to datediff to do this. This syntax is quite confusing to me at this stage. Could you please explain what is happening here.
    Cheers for the example!
  5. FrankKalis Moderator

    I think that is also explained in the article.
    Anyway, the "trick" behind this is to know that a DATETIME value always contains a date AND a time part. The earliest time for any given date is 00:00:00.000, e.g. midnight. This knowledge is applied when you know what DATEDIFF really does. It returns the difference according to the interval you have chosen in whole integer numbers. So, for any given time portion DATEDIFF returns the same number between the two dates in the comparison. So, after applying DATEDIFF you have the number of days between the two dates. The wrapping DATEADD function is just to convert the integer result from DATEDIFF back into a DATETIME data type. During this conversion back SQL Server has to "assume" something about the time portion. By default it will "assume" the minimum time for the given date and that is midnight. [:)]
    No magic, and much faster than the commonly referenced CONVERT methods.
  6. MichaelB Member

    Didnt know it was faster. cool.
    Thanks!
  7. FrankKalis Moderator

    Yes, it is. Although I believe no one has yet performed kind of scientific research and testing here.
  8. tenderfoot New Member

    Hello Frank,
    Read through your article, but a lot of the vocab/concepts are double dutch to me. I read the part about searching for a specific period time of time using

    > lowestDate And < highestDate
    But I do not think this will work. I am confused. How can i adjust the time to midnight?
    Cheers,
  9. Adriaan New Member

    See Madhivanan's post immediately before your post.
    By the way, this is a very poor data design, if you ask me. You're using a single column (Value) to store all kinds of different data, so as soon as you need to do anything interesting with that value, you have to add code to handle type-specific issues. This is begging for problems, if you have any control over the data structure then just stick to 4th Normal Form.
  10. tenderfoot New Member

    Hiya,
    I used Madhivanan's post example. Could you explain it for me? All I can fathom from it is this:
    CAST(ceav_startdate.Value as datetime) >= dateadd(day,datediff(day,0,@Today),0)
    1. Cast the date to datetime datatype
    2. This date is > = (add to the 'day part' of the date (an interval of 0 to the today's date)).
    It makes no sense to me. am I interpreting this correctly? It seems al little convoluted to do something so simple.
    "You're using a single column (Value) to store all kinds of differentdata, so as soon as you need to do anything interesting with thatvalue, you have to add code to handle type-specific issues."

    This is a structure I have inherited. Is there no function to cast a date as just a date? Why is date and time lumped together? I do not actually need to process the time, just the date.
    Thanks.
  11. Adriaan New Member

    There are two bits of convolution ...
    #1 is that your Value column is not a true DATETIME column - presumably it is VARCHAR. This is what I was commenting about earlier.
    #2 is that SQL does not have a true DATE data type, only DATETIME, and that GETDATE() always includes the current time of day - which means that if you want to ignore the time part, you have to apply a function that eliminates the time part for you.
    The cleanest solution for #2 really is DATEADD(d, DATEDIFF(d,0,date_expression),0)
    Another solution is to use CONVERT with an appropriate switch for the date format, but trust me - the DATEADD+DATEDIFF combination is fail-proof.
    Just memorize DATEADD with DATEDIFF - perhaps it is easier to remember like this:
    DATEADD(d,0,
    DATEDIFF(d,0,date_expression))
    -- note that you can put the 0 for DATEADD before the DATEDIFF, without it making a difference.
    It also comes in handy when you need the first of the month, or the first of the week - just change the d parameters to m or w.
  12. MichaelB Member

    Call me crazy, but I really like select
    cast(convert(varchar(10),getdate(),110) as smalldatetime)
    Never had an issue, but again, maybe I am missing something since we do dates differently:)
  13. MichaelB Member

    of course with an avitar like mine... you sure would!
  14. Madhivanan Moderator

    [quote user="Adriaan"]
    Just memorize DATEADD with DATEDIFF - perhaps it is easier to remember like this:
    DATEADD(d,0,
    DATEDIFF(d,0,date_expression))
    -- note that you can put the 0 for DATEADD before the DATEDIFF, without it making a difference.
    It also comes in handy when you need the first of the month, or the first of the week - just change the d parameters to m or w.
    [/quote]
    Note that the above method wont work properly if 0 is preceeded by datediff

    Select
    dateadd(day,datediff(day,0,getdate()),0),
    dateadd(day,0,datediff(day,0,getdate()))
    Select
    dateadd(month,datediff(month,0,getdate()),0),
    dateadd(month,0,datediff(month,0,getdate()))

    Select
    dateadd(year,datediff(year,0,getdate()),0),
    dateadd(year,0,datediff(year,0,getdate()))
    There is problem with that approach if the parameter is not day
  15. Adriaan New Member

    Good catch, Madhivanan. I didn't check the month and year calculations, which obviously do require the correct order. By the way, I put in the wrong switch for the week: it is wk, not w. You can also use qq to find the start of the current quarter, and hh or mi for rounding your time. (Note that using s will cause a run-time overflow.) You can only use the 0 before the DATEDIFF with the d switch.
    MikeEBS - using CONVERT is nice and fine if you can safely predict the Windows date settings on all the servers where your database will reside. The 110 switch is the US format, which may be widely used, but not outside of the US. The DATEADD with DATEDIFF function is a transparent solution - the date format doesn't come into play, which may also be a reason why it is a faster solution.
  16. MichaelB Member

    Adriaan,
    I agree. all my DBs are US based so no worries. Thanks for the info:)

Share This Page