GETDATE() dose not.........

arshad mahmood · August 18th, 2003, 11:21 PM

Hello Sir
How can i retreive the records froem table where data =system date.
i have wrote the following query but it gives the error:
SELECT * FROM Table WHERE Table.[Date]=CURDATE();
it gives the syntax error.
Can Anyone guide me what is the problem with this.
I have also use the getdate() but it dose not retutn
any result.
Thanks

arshad mahmood

melvik · August 19th, 2003, 12:22 AM

bc it matches the time also:
use

Code:

SELECT *
FROM Deputy
WHERE ApplyDate = CAST(GETDATE() AS varchar(12))

Always:),
Hovik Melkomian.

Jeff Mason · August 19th, 2003, 07:57 AM

CURDATE() is not a valid SQL Server function, which is why you got the syntax error. Use Getdate() or Current_Timestamp (SQL-92 standard) instead.

If your query using GetDate() is something like:

Code:

    SELECT * FROM YourTable WHERE SomeDateColumn=GetDate();

then this will only select rows whose date column has the value of exactly 'right now', which is quite unlikely. Another poster suggested a WHERE Clause along the lines of:

Code:

 ... WHERE SomeDateColumn = CAST(GETDATE() AS varchar(12))

but this assumes that your date column is a character string datatype exactly formatted to the server's default date format. We leave aside the discussion for another day why storing dates this way is a bad idea.

Since columns of type datetime (or smalldatetime) store both the date and the time, and since 'Getdate()' or its equivalent, 'current_timestamp' return a datetime datatype, your WHERE clause is testing for equality of both the date and the time. Since it is unlikely that you have any rows inserted into your database with the time value of exactly right now, this is why you are not getting any rows returned.

I assume you only care about the date portion, and are interested in rows where the date column is equal to today's date, without regards to the time.

To do this, you need to ignore the time portion of the datetime column. There are a few ways to do this, but the most straightforward is to convert the datetime value to a string which is only long enough to hold the date portion of the value. You then convert the resultant string back to a datetime. Since the string only contains a date, the time is set to midnight by default. Thus, you set the time portion of the values to midnight, which effectively ignores the time:

Code:

    ...WHERE CAST(CONVERT(char(8),SomeDateColumn,112) as datetime) = CAST(CONVERT(char(8),GetDate(),112) as datetime);

The CONVERT function is SQL Server proprietary and takes a style parameter (the '112' above, see BOL) which indicates that the function should return datetime in the form 'yyyymmdd'. This string is CAST back to a datetime, thus setting the time to midnight. Since this is done for both sides of the equality test, the effect is that the date portions only are compared.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com