SQL - Dates

Date values are stored in date table columns in the form of a timestamp. A SQL timestamp is a record containing date/time data, such as the month, day, year, hour, and minutes/seconds. It's not much different from the standard date format.

Date Columns:

Column Type	Format
time	HH:MM:SS
date	YYYY-MM-DD
datetime	YYYY-MM-DD HH:MM:SS

Date values are stored in the form of a timestamp, and SQL offers a built-in function called GETDATE() that returns the current date in the form of a SQL timestamp.

SQL SELECT GETDATE():

USE mydatabase;

SELECT GETDATE();

Timestamp Result:

2004-06-22 10:33:11.840

SQL expects dates to be formatted as above but does offer some flexibility when working with dates inside query statements. For instance, date values do not necessarily need to contain the hour, minutes, and seconds values. SQL also accepts most traditional date formats such as "MM/DD/YY" (ex: "01/01/06").
Using a built in function, ISDATE() we can do some testing on date values to see if they meet the formatting requirements.

SQL Code:

USE mydatabase;

SELECT 
ISDATE('8/24/08') AS "MM/DD/YY",
ISDATE('2004-12-01') AS "YYYY/MM/DD";

ISDATE() returns a 1 or a 0 indicating a true or false result. In this case, both formats are acceptable date formats as a 1 value was returned.

SQL - Month(), Day(), Year()

The Month(), Day() and Year() functions all extract corresponding values from a given date.

SQL Year():

USE mydatabase;

SELECT YEAR(GETDATE()) as "Year";
SELECT YEAR('8/14/04') as "Year";

SQL Month():

USE mydatabase;

SELECT MONTH(GETDATE()) as "Month";
SELECT MONTH('8/14/04') as "Month";

SQL Day():

USE mydatabase;

SELECT DAY(GETDATE()) as "Day";
SELECT DAY('8/14/04') as "Day";

Understanding timestamps and extracting pieces of dates is the first step in being able to perform date calculations and work more in-depth with SQL Dates.

Posted in: SQL