Results 1 to 6 of 6

Thread: Best way to ignore time in datetime comparisons

  1. #1
    tc Guest

    Best way to ignore time in datetime comparisons


    What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22

  2. #2
    Kristine Greenlee Guest

    Best way to ignore time in datetime comparisons (reply)


    CONVERT(char(10), datetimefield, 101) and then do string comparisons.

    ------------
    tc at 9/27/00 11:38:40 AM


    What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22

  3. #3
    Jim W Guest

    Best way to ignore time in datetime comparisons (reply)

    Uhhhh...101 is -not- the best way. That returns mm/dd/yyyy. Doing a date range on that will return invalid results. You CAN use convert 111 which gives yyyy/mm/dd, but it -might- be slower since you are comparing strings.

    Usually the way I do a date range and ignore their time is as follows: (note, I prefer this method myself, but obviously not everyone will)
    --
    declare @startarg char(10), @endarg char(10)

    set @startarg = '1-1-2000'
    set @endarg = '1-31-2000'

    declare @enddate datetime

    set @enddate = convert(datetime, @endarg + ' 11:59:59:997&#39

    select * from table where datefield between @startarg and @enddate
    --

    Another way to do it, although I never use it, it to user


    ------------
    Kristine Greenlee at 9/27/00 11:53:16 AM


    CONVERT(char(10), datetimefield, 101) and then do string comparisons.

    ------------
    tc at 9/27/00 11:38:40 AM


    What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22

  4. #4
    Kristine Greenlee Guest

    Best way to ignore time in datetime comparisons (reply)

    Why would it return invalid results??

    Using tc's examples:

    SELECT * from table where CONVERT(char(10), datetimefield, 101) = '07/08/1996'

    OR

    SELECT * from table where CONVERT(char(10), datetimefield, 101) BETWEEN '01/01/1999' AND '04/01/1999'

    It does convert to mm/dd/yyyy, but if the date you are looking for is in the same format, then you are comparing apples to apples. If you the date you are looking for is in another format, then simply choose the corresponding style argument for the CONVERT function.

    It works.
    ------------
    Jim W at 9/27/00 3:15:59 PM

    Uhhhh...101 is -not- the best way. That returns mm/dd/yyyy. Doing a date range on that will return invalid results. You CAN use convert 111 which gives yyyy/mm/dd, but it -might- be slower since you are comparing strings.

    Usually the way I do a date range and ignore their time is as follows: (note, I prefer this method myself, but obviously not everyone will)
    --
    declare @startarg char(10), @endarg char(10)

    set @startarg = '1-1-2000'
    set @endarg = '1-31-2000'

    declare @enddate datetime

    set @enddate = convert(datetime, @endarg + ' 11:59:59:997&#39

    select * from table where datefield between @startarg and @enddate
    --

    Another way to do it, although I never use it, it to user


    ------------
    Kristine Greenlee at 9/27/00 11:53:16 AM


    CONVERT(char(10), datetimefield, 101) and then do string comparisons.

    ------------
    tc at 9/27/00 11:38:40 AM


    What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22

  5. #5
    Jim W Guest

    Best way to ignore time in datetime comparisons (reply)

    It won't if you are searching through multiple years. Try it.

    If the data has rows with the datetimefield as follow:

    01/01/1999
    03/01/1999
    05/01/1999
    07/01/1999
    05/01/2000
    09/01/1999

    and you use:

    SELECT * from table where CONVERT(char(10), datetimefield, 101) BETWEEN '01/01/1999' AND '04/01/1999'

    you will also get the rows with datetimefield of 05/01/2000. This is because the '05/01/2000' is between '01/01/1999' and '04/01/1999' when you are comparing strings.

    ------------
    Kristine Greenlee at 9/27/00 3:53:40 PM

    Why would it return invalid results??

    Using tc's examples:

    SELECT * from table where CONVERT(char(10), datetimefield, 101) = '07/08/1996'

    OR

    SELECT * from table where CONVERT(char(10), datetimefield, 101) BETWEEN '01/01/1999' AND '04/01/1999'

    It does convert to mm/dd/yyyy, but if the date you are looking for is in the same format, then you are comparing apples to apples. If you the date you are looking for is in another format, then simply choose the corresponding style argument for the CONVERT function.

    It works.

  6. #6
    Kristine Greenlee Guest

    Best way to ignore time in datetime comparisons (reply)

    Okay, you're right. It works find for a simple '=', but for a BETWEEN it does provide unexpected results like you said.

    Style 121 - yyyy-mm-dd is correct.

    ------------
    Jim W at 9/27/00 4:25:16 PM

    It won't if you are searching through multiple years. Try it.

    If the data has rows with the datetimefield as follow:

    01/01/1999
    03/01/1999
    05/01/1999
    07/01/1999
    05/01/2000
    09/01/1999

    and you use:

    SELECT * from table where CONVERT(char(10), datetimefield, 101) BETWEEN '01/01/1999' AND '04/01/1999'

    you will also get the rows with datetimefield of 05/01/2000. This is because the '05/01/2000' is between '01/01/1999' and '04/01/1999' when you are comparing strings.

    ------------
    Kristine Greenlee at 9/27/00 3:53:40 PM

    Why would it return invalid results??

    Using tc's examples:

    SELECT * from table where CONVERT(char(10), datetimefield, 101) = '07/08/1996'

    OR

    SELECT * from table where CONVERT(char(10), datetimefield, 101) BETWEEN '01/01/1999' AND '04/01/1999'

    It does convert to mm/dd/yyyy, but if the date you are looking for is in the same format, then you are comparing apples to apples. If you the date you are looking for is in another format, then simply choose the corresponding style argument for the CONVERT function.

    It works.

Posting Permissions

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