none
How to compare a Date with GetDate() in SQL Server 2000 ?

    Question

  • Hi All,

        I would like to compare a table date with the system date and retrieve records based on that. I've tried the following code and its taking more time to retrieve the data. Is there any other way to do so?

    -- Original Code
    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= GetDate()
    -- My Code
    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE Convert(Char(10),DateColumn,20) >= Convert(Char(10), GetDate(),20)
     

    Using the original code retrieves the records in 2 seconds where as my code takes 10-12 seconds. The reason I am converting the date is the Date Column has a Time Stamp of 00:00:00.000 where as the GetDate gets the current timestamp. So, I am  afraid of missing a few records.

    PS:

    Date Column Field has the values like 2010-02-10 00:00:00.000

    Is there a better way to do it?

    Thanks

    Bangaaram


    Known is a DROP, Unknown is an OCEAN.




    • Edited by Bangaaram Wednesday, August 08, 2012 2:12 PM
    Wednesday, August 08, 2012 1:49 PM

Answers

  • It is not going to be re-calculated for every row and is calculated only once per query. However, there is some other problem involving datediff function that was discussed in this forum before. There is a possibility that the version with the variable will be faster.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 21, 2012 3:56 AM
    Moderator

All replies

  • The reason your code is taking longer is because an index cannot be used if you use the Convert() function. For performance reasons, using functions in a WHERE clause is a bad idea.
    Wednesday, August 08, 2012 1:53 PM
  • You do not need to do the conversion as the conversion can lead to non-indexed search/scan and this is why you've got longer search time!

    Many Thanks & Best Regards, Hua Min



    Wednesday, August 08, 2012 1:53 PM
  • The reason your code is taking longer is because an index cannot be used if you use the Convert() function. For performance reasons, using functions in a WHERE clause is a bad idea.

    Could you suggest a possible solution for this then?

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 1:54 PM
  • Use

    Convert(varchar(20),getdate(),11)

    to compare the dates


    Many Thanks & Best Regards, Hua Min


    I don't see any difference of using the above code. It still takes the same time to retrieve records.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 1:58 PM
  • As far as I can see, you do not have to convert the GetDate() to do the comparison. Your DateColumn does have a time part that is always 00:00:00.00000:00:00.000, meaning midnight. If GetDate() produces a random moment during a day, as long as the date part of GetDate() is smaller than the date part of your DateColumn, the WHERE clause will include the records. When you manage to execute GetDate() at exactly midnight, the WHERE clause will leave out all records of this day. But by doing the convert, your semantics do not change. The same records will be left out. I would recommend to just use the first statement.

    Or is the real problem that you are not sure if DateColumn sometimes does have a time component other than 00:00:00.00000:00:00.000?

    Wednesday, August 08, 2012 1:58 PM
  • Could you suggest a possible solution for this then?

    Known is a DROP, Unknown is an OCEAN.

    To remove the time portion in getdate(), please try this instead

    CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112)


    Many Thanks & Best Regards, Hua Min


    Wednesday, August 08, 2012 2:01 PM
  • As your comparison is ">" and not ">=", I don't think you would be loosing any records without applying CONVERT unless you don't want records of same date and DateColumn differs with GETDATE on time alone.

    For example, if you want your data to be retrieved if DateColumn = '2012-08-08 12:00:00' AND GETDATE() = '2012 08-08 11:55:55' then you may not need to apply data conversion logic.

    Thanks!

    Wednesday, August 08, 2012 2:03 PM
  • As far as I can see, you do not have to convert the GetDate() to do the comparison. Your DateColumn does have a time part that is always 00:00:00.00000:00:00.000, meaning midnight. If GetDate() produces a random moment during a day, as long as the date part of GetDate() is smaller than the date part of your DateColumn, the WHERE clause will include the records. When you manage to execute GetDate() at exactly midnight, the WHERE clause will leave out all records of this day. But by doing the convert, your semantics do not change. The same records will be left out. I would recommend to just use the first statement.

    Thanks Chris. But, how about when doing comparision with an '='. Say for example:

    SELECT Id, Name, DateColumn
    FROM Table1
    WHERE DateColumn >= GetDate()

    The TimeStamp in GetDate is never equal to the DateColumn TimeStamp.

    If the DateColumn has a value of 2012-08-08 00:00:00.000 and the GetDate() gives a value of 2012-08-08 10:06:11.123, it never matches up the values right?

    I am just confused only about this. Please help me  in understanding this.

    Thanks


    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:07 PM
  • As your comparison is ">" and not ">=", I don't think you would be loosing any records without applying CONVERT unless you don't want records of same date and DateColumn differs with GETDATE on time alone.

    For example, if you want your data to be retrieved if DateColumn = '2012-08-08 12:00:00' AND GETDATE() = '2012 08-08 11:55:55' then you may not need to apply data conversion logic.

    Thanks!


    I am sorry. I've forgot to mention that. I am using ">=" and not only ">".

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:09 PM
  • Could you suggest a possible solution for this then?

    Known is a DROP, Unknown is an OCEAN.

    To remove the time portion in getdate(), please try this instead

    CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112)


    Many Thanks & Best Regards, Hua Min



    Nice idea but this makes things more complicated.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:11 PM
  • If you want to omit the time portion, you can try this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112);


    Many Thanks & Best Regards, Hua Min

    Wednesday, August 08, 2012 2:13 PM
  • If you want to omit the time portion, you can try this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112);


    Many Thanks & Best Regards, Hua Min


    I did understand that but, its still taking the same time to retrieve records.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:15 PM
  • Try an alternate query provided below and see if it results what you expect (and in less time). Ignore if it takes more time than your version.

    SELECT	Id, Name, DateColumn
    FROM	Table1
    WHERE	DATEADD(dd, 1, DateColumn) >= GetDate()
    AND		DATEDIFF(dd, GETDATE(), DateColumn) >= 0
    Thanks!
    Wednesday, August 08, 2012 2:18 PM
  • You could use

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(DateTime, CONVERT(Char(10), GETDATE(), 102))

    The Convert(Char(10), GetDate(), 102) will convert the system date to a character string without time component.

    The Convert(DateTime, ...) will convert it back to a datetime so that you can compare it to DateColumn without having to type cast the DateColumn, so the index on DateColumn can still be used.

    Wednesday, August 08, 2012 2:19 PM
  • Try an alternate query provided below and see if it results what you expect (and in less time). Ignore if it takes more time than your version.

    SELECT	Id, Name, DateColumn
    FROM	Table1
    WHERE	DATEADD(dd, 1, DateColumn) >= GetDate()
    AND		DATEDIFF(dd, DateColumn, GETDATE()) >= 0
    Thanks!

    Got the following error:

    Msg 517, Level 16, State 1, Line 1
    Adding a value to a 'datetime' column caused overflow.
    (0 row(s) affected)


    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:22 PM
  • You could use

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(DateTime, CONVERT(Char(10), GETDATE(), 102))

    The Convert(Char(10), GetDate(), 102) will convert the system date to a character string without time component.

    The Convert(DateTime, ...) will convert it back to a datetime so that you can compare it to DateColumn without having to type cast the DateColumn, so the index on DateColumn can still be used.


    No, that takes the same time as of my query.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:26 PM
  • If you want to omit the time portion, you can try this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112);


    Many Thanks & Best Regards, Hua Min


    I did understand that but, its still taking the same time to retrieve records.

    Known is a DROP, Unknown is an OCEAN.

    Then try

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE CONVERT(varchar(12),DateColumn, 112) >= CONVERT(varchar(12),GETDATE(), 112);

    Moreover, for the search speed issue, you can consider to add one more column that holds this value

    CONVERT(varchar(12),DateColumn, 112)

    and such column can be indexed too!


    Many Thanks & Best Regards, Hua Min



    Wednesday, August 08, 2012 2:28 PM
  • If you want to omit the time portion, you can try this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= CONVERT(datetime,CONVERT(varchar(12),GETDATE(), 112), 112);


    Many Thanks & Best Regards, Hua Min


    I did understand that but, its still taking the same time to retrieve records.

    Known is a DROP, Unknown is an OCEAN.

    Then try

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE CONVERT(varchar(12),DateColumn, 112) >= CONVERT(varchar(12),GETDATE(), 112);


    Many Thanks & Best Regards, Hua Min


    I've tried all of these and they still take the same amount of time as my query.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:31 PM
  • If you want to include the current date, then you put ">=". If not, just put ">".

    Many Thanks & Best Regards, Hua Min

    Wednesday, August 08, 2012 2:43 PM
  • If you want to include the current date, then you put ">=". If not, just put ">".

    Many Thanks & Best Regards, Hua Min


    Yes, I want to include the current date and thats the reason I'm asking help here despite of not getting the expected results.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 2:47 PM
  • Did you try it? By this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE CONVERT(varchar(12),DateColumn, 112) >= CONVERT(varchar(12),GETDATE(), 112);

    it'll only check the date part.


    Many Thanks & Best Regards, Hua Min

    Wednesday, August 08, 2012 3:01 PM
  • This will give you today without the time portion, although I am not sure if it will be any faster:

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= DATEADD(day, DATEDIFF(day, '19000101', getdate()), '19000101')

    Wednesday, August 08, 2012 3:04 PM
  • Did you try it? By this

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE CONVERT(varchar(12),DateColumn, 112) >= CONVERT(varchar(12),GETDATE(), 112);

    it'll only check the date part.


    Many Thanks & Best Regards, Hua Min


    Yes, I agree that it checks only the date part but, it still takes the same amount of time to retrieve the records.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 3:08 PM
  • This will give you today without the time portion, although I am not sure if it will be any faster:

    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= DATEADD(day, DATEDIFF(day, '19000101', getdate()), '19000101')


    Yes, returned the same number of rows but it was slower too.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 3:11 PM
  • Can you show with some data?

    1. What you can see or cannot see by not using convert?

    2. What you can see or cannot see by using convert?


    Many Thanks & Best Regards, Hua Min

    Wednesday, August 08, 2012 3:13 PM
  • Slower huh?  That's a bummer.  Maybe try this, which will get you away from using the functions in the where clause?

    ;with cteDateCompare as
    (
    	Select	Cast(Convert(Char(10), GetDate(),20) As datetime) As dtDate
    )
    SELECT Id, Name, DateColumn
    FROM
    Table1 t1
    join cteDateCompare c on 1=1
    WHERE DateColumn >= c.dtDate


    Edit:

    Forgot you were on SQL 2000, this won't work.  Can you maybe join a derived table to do the same?

    SELECT Id, Name, DateColumn
    FROM
    Table1 t1
    join	(
    			Select	Cast(Convert(Char(10), GetDate(),20) As datetime) As dtDate
    		)  c on 1=1
    WHERE DateColumn >= c.dtDate
    I can't test right now, since my SSMS is locked up with some other things...
    • Edited by dgjohnson Wednesday, August 08, 2012 3:17 PM
    Wednesday, August 08, 2012 3:14 PM
  • Can you show with some data?

    1. What you can see or cannot see by not using convert?

    2. What you can see or cannot see by using convert?


    Many Thanks & Best Regards, Hua Min


    Sorry, I was just re-checking your query and it seems to be working good same as the original query. Thanks for your help.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, August 08, 2012 4:02 PM
  • Take a look at this blog post explaining the date time queries:

    Bad habits to kick : mis-handling date / range queries

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, August 19, 2012 1:41 PM
    Moderator
  • The GETDATE() function is non-deterministic, which means it is recalculated for every row in the table for the compare.  This should be faster:


    DECLARE @now DATETIME
    SET @now = CONVERT(datetime,Convert(Char(10), GetDate(),20),20)
    SELECT Id, Name, DateColumn
    FROM
    Table1
    WHERE DateColumn >= @now

    Monday, August 20, 2012 9:16 PM
    Moderator
  • It is not going to be re-calculated for every row and is calculated only once per query. However, there is some other problem involving datediff function that was discussed in this forum before. There is a possibility that the version with the variable will be faster.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 21, 2012 3:56 AM
    Moderator
  • Thanks man. I appreciate your help.
    Friday, November 15, 2013 10:03 PM
  • >WHERE Convert(Char(10),DateColumn,20) >= Convert(Char(10), GetDate(),20)

    The above is no-no! Not SARGable:

    http://www.sqlusa.com/bestpractices/sargable/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, November 17, 2013 10:15 PM
    Moderator