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.

Of the different capabilities offered by SQL engines, dates are one of the most nonstandard areas. You'll need to be sure to check your database engine documentation for each of these functions and make sure that it is supported. Also make sure you know about any different parameters that may be required by your specific engine.
In the near future, products will become more and more standardized and will support these functions directly and identically. For now, just be sure you check the documentation.
Dates can be formatted differently in different engines. For example, with some engines, the date can be shown as 05/02/99, May 2, 1999, and 02 May 1999, with all being recognized as valid representations of the date. In this case, the engine will recognize the information as a date and will save the value correctly. In other engines, it may be necessary to present the date exactly as you have dates defined on your system, which takes into account localization for different countries' representation of dates. It may also be necessary to add a pound sign (#), before and after the date. Again, be sure to check the documentation for your specific engine to determine if there are any of these types of requirements.

Sometimes it is quite useful to be able to extract individual parts of a date, such as day of the month, month, or year. To do this, SQL provides the DAY(), MONTH(), and YEAR() functions. These are supported by all of the database systems except Oracle. These functions perform in the same manner, and the syntax for each is as follows:

1. DAY(date)
2. MONTH(date)
3. YEAR(date)

The following code displays the DateOfBirth of each film club member, as well as the day of the month, month, and year of their birth:

SELECT DateOfBirth, DAY(DateOfBirth), MONTH(DateOfBirth), YEAR(DateOfBirth)
FROM MemberDetails
ORDER BY YEAR(DateOfBirth);

Executing the preceding statement provides the following results:

SELECT @@version;

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';

SELECT NAME, HIRE_DATE
FROM SALESREPS
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.