SQL GETDATE, SYSDATE, and TODAY

Nearly every database engine will have a function that returns the current date. You will need to check the documentation with your engine, but it will typically be

1. GETDATE,
2. SYSDATE, or
3. TODAY.

These functions can be used as part of a separate function call. You can use these functions if you have a value in a column and want to compare it to today's date to determine the difference in the number of days, weeks, or other time interval that you are interested in.
For example, if you have a column named MyDate in your table named MyTable and want to get the number of weeks between MyDate and today's date, the following statement will do the trick:

SELECT DATEDIFF(week, GETDATE(), MyDate) 
FROM MyTable

This assumes that your engine supports GETDATE. You will have to substitute the correct function call for your engine. Here is an example of this function using the PUBS database:

SELECT DateDiff(day, getdate(), pubdate) from titles

Some SQL products, including SQL Server, provide access to system values through built-in functions rather than symbolic constants. The SQL Server version of the preceding query is

SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE > GETDATE();

In Oracle, the datetime system function is SYSDATE. Oracle 9i and later versions support CURRENT_DATE and CURRENT_TIMESTAMP.

SELECT SYSDATE AS "Date"
FROM DUAL;

SYSDATE returns the system date and time but does not display the time unless formatted to do so with the function TO_CHAR():

SELECT TO_CHAR(SYSDATE,
➝ ’YYYY-MM-DD HH24:MI:SS’)
FROM DUAL;

A number of useful built-in functions are specified in SQL standard, and most SQL implementations add many more. These facilities often provide data type conversion facilities. For example, DB's built-in MONTH() and YEAR() functions take a DATE or TIMESTAMP value as their input and return an integer that is the month or year portion of the value. This query lists the name and month of hire for each salesperson in the sample database:

SELECT NAME, MONTH(HIRE_DATE)
FROM SALESREPS;

and this one lists all salespeople hired in 2016:

SELECT NAME, MONTH(HIRE_DATE)
FROM SALESREPS
WHERE YEAR(HIRE_DATE) = 2016;

Built-in functions are also often used for data reformatting. Oracle's built-in TO_CHAR() function, for example, takes a DATE data type and a format specification as its arguments and returns a string containing a formatted character string version of the date. (This same function is also capable of converting numeric values to formatted character strings.) In the results produced by this query:

SELECT NAME, TO_CHAR(HIRE_DATE,'DAY MONTH DD, YYYY')
FROM SALESREPS;

the hire dates will all have the format "Wednesday June 14, 2017" because of the built-in function. In general, a built-in function can be specified in a SQL expression anywhere that a constant of the same data type can be specified. The built-in functions supported by popular SQL dialects are too numerous to list here.
The IBM DB2 SQL dialects include about two dozen built-in functions, Oracle supports a different set of about two dozen built-in functions, and SQL Server has several dozen. The SQL2 standard incorporated the most useful built-in functions from these implementations, in many cases with slightly different syntax. These functions are summarized in Table 5-16.
TABLE 5-16 SQL Standard Built-In Functions

These are standard SQL functions that are commonly used for data manipulation and querying in SQL databases.

Take this brief quiz to make sure you understand the date functions.
SQL Date - Quiz