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?
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading