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 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():
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.
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.
SELECT YEAR(GETDATE()) as "Year";
SELECT YEAR('8/14/04') as "Year";
SELECT MONTH(GETDATE()) as "Month";
SELECT MONTH('8/14/04') as "Month";
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.
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!