• Eirikur Eiriksson - Sunday, November 12, 2017 2:57 AM

    VegasL - Saturday, November 11, 2017 10:44 PM

    Hi,

    I have in a data row:  column [date]   2017-11-06 01:17:34.000

    I only want to search for values if the year is 2015.  How do I do this?

    where [date] like '%2015"          ?

    This is a datetime value, neither a string nor date value, constrain the search accordingly, i.e. 
    WHERE [date] > CONVERT(DATEDIME,'2014-12-31 23:59',126)
    AND [date] < CONVERT(DATEDIME,'2016-01-01 00:00',126)

    😎

    This will include records that you don't want to include.  When using date ranges, you should be using half-closed intervals.  Most people use closed intervals, but this goes to the other extreme and uses open intervals.  Neither is correct for most uses.  Specifically, this method will include dates between 2014-12-31 23:59:00.003 and 2014-12-31 23:59:59.997 that you do not want to include.  The correct formulation is as follows.

    WHERE [date] >= CAST('2015-01-01' AS DATETIME)
    AND [date] < CAST('2016-01-01' AS DATETIME)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA