| Lesson 12 || Date functions |
| Objective || Issues related to date functions |
Issues surrounding date functions in SQL
One of the interesting things about date information is how it is queried. The engine that you use will typically make some educated guesses on your behalf when you query a date column. For example, when information is stored into a date column and you don't indicate a time, the time will default to a predetermined value. Typically, this is 12:00 AM. So, if you simply query a column and only ask for a date, you will get back only those values with the default time.
To get around this, you need to do one of several things. The easiest is to use the
LIKE operator and use % as a wildcard character:
SELECT * FROM MyTable
WHERE MyDate LIKE 'May 3, 2009%'
This query will return all rows in which the date is May 3, 2009 and the time is any value.
Later, we will see functions that will return only the portion of the date you are interested in. Be sure to check the documentation for your database engine to learn how it handles date functions. Like other functions in SQL, date functions can be affected by important engine differences
Universal Support for dates in SQL
Fortunately, with the advent of the year 2000 conversion, most DBMS vendors added universal support for dates in SQL statements with four-digit years in a standard YYYYMM-DD format, which we use for most of the examples in this course. In Oracle’s case, the default format is still as shown in the preceding examples, but it can be changed at either the database or user session with a simple command. If you are using Oracle and you try any of the examples in this book, simply enter this command to change your default date format:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Care must be taken when forming queries that search for exact date matches using the equal (=) operator, and the dates have time components stored in them. Consider the following example:
SELECT NAME, HIRE_DATE
WHERE HIRE_DATE = '06/14/2017';
If a salesperson’s hire date were stored in the database as noon on June 14, 2017, the salesperson would not be included in the query results from Oracle or SQL Server databases. The DBMS would assume a time of midnight for the string supplied with the SQL statement,
and since midnight is not equal to noon, the row would not be selected. On the other hand, for a DB2 database, where the time is not stored with a DATE data type, the row would appear in the query results.
Finally, starting with SQL2, the ANSI/ISO standard added support for date/time data with a set of data types based on, but not identical to,
the DB2 types. In addition to the DATE, TIME, and TIMESTAMP data types, the standard specifies an INTERVAL data type, which can
be used to store a time interval (for example, a timespan measured in days, or a duration measured in hours, minutes, and seconds).
The standard also provides a very elaborate and complex method for dealing with date/time arithmetic, specifying the precision of intervals,
adjusting for time zone differences, and so on. Most SQL implementations now have support for these standard types. One notable exception, however, is that SQL Server has long used the TIMESTAMP data type for an entirely different purpose, so supporting the ANSI/ISO
specification for it presents a very real challenge. As these examples illustrate, the subtle differences in data types among various SQL
products lead to some significant differences in SQL statement syntax.
They can even cause the same SQL query to produce slightly different results on different database management systems. The widely praised portability of SQL is thus true but only at a general level. An application can be moved from one SQL database to another,
and it can be highly portable if it uses only the most mainstream, basic SQL capabilities.
However, the subtle variations in SQL implementations mean that data types and SQL statements must almost always be adjusted somewhat if
they are to be moved across DBMS brands. The more complex the application, the more likely it is to become dependent on
DBMS-specific features and nuances, and the less portable it will become.
In the next lesson, we will start looking at the date functions themselves.