• Alvin Ramard (1/20/2015)


    kabaari (1/19/2015)


    Alvin and Andrew,

    Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!

    WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'

    Casting to DateTime instead of date will cause you problems with c.dateentered having values during the day on 2015-01-19 and has a value of 2015-01-09 01:02:03, for example.

    Casting dateentered to DATE or DATETIME will make the query non-SARGable, meaning it cannot use a index for anytime except an index scan. Also, DATE was not a valid datatype in SQL Server 2005.

    Also, dates in the format '2014-11-23' and '2015-01-19' are not in an unambiguous format, meaning they can produce unexpected values or conversion errors depending on the setting of DATEFORMAT. I prefer using the unambiguous date format YYYYMMDD.

    set dateformat ydm

    select T1 = convert(datetime,'20141123')

    select T2 = convert(datetime,'2014-11-23')

    Results:

    T1

    -----------------------

    2014-11-23 00:00:00.000

    T2

    -----------------------

    Msg 242, Level 16, State 3, Line 3

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Assuming that the column dateentered is DATETIME (or even DATE or DATETIME2), the following selection criteria of greater than or equal to start date and less then the day after the end date using the unambiguous date format YYYYMMDD is probably the easiest way to get a SARGable query.

    where

    c.dateentered >= '20141123' and

    c.dateentered < '20150120'