Problem solve Get help with specific problems with your technologies, process and projects.

Date constraint in a query

How can you put a date constraint, for example, a date entered to be less than the system date, i.e., a date that has already passed?

How can you put a date constraint, for example, a date entered to be less than the system date, i.e., a date that...

has already passed?

The answer is to use the standard SQL reserved word CURRENT_DATE which represents—wait for it—the current system date.

 where datecol < current_date 

Some database systems don't support all facets of standard SQL, and instead use their own functions for the current date. For example, SQL Server uses GETDATE() --

 where datecol < getdate() 

Note that GETDATE() here is actually a datetime value, because it contains both date and time. So if we want rows for all dates that have actually passed, we have to do some fancy footwork to manipulate the GETDATE value into a value at midnight. One method is to strip out the date value using conversion to a string, then convert back to a datetime --

 where datecol
         < convert(datetime
             , convert(char(10),getdate(),120) ) 

Converting to a CHAR(10) string effectively strips off the time. Format style 120 means YYYY-MM-DD. There's nothing wrong with a character string that contains a date without a time (it's just a string, after all). Then this string is converted back to a datetime value, with the result that the time component is set to midnight, i.e., 00:00:00.000.

However, the above method is actually much slower than doing integer arithmetic on the date value --

 where datecol
         < dateadd(d, datediff(d,0,getdate()), 0) 

This expression first calculates the number of days between the "zero date" and the current datetime, then adds that number of days back to 0. Sweet, eh?

This was last published in June 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.