[SQL] SQL DateTime Functions

SQL - Timestamp
A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be converted or manipulated in nearly every way imaginable.

SQL Code:
SELECT CURRENT_TIMESTAMP;
Return a Timestamp:2004-06-22 10:33:11.840
Keep in mind that each platform of SQL (DB2, Oracle, SQL Server, etc...) may return dates and times which are formatted differently.
SQL- Date FunctionsAs we just mentioned, it is possible to breakdown timestamps into their individual pieces using any of the following date functions.

SQL Code:
SELECT MONTH(CURRENT_TIMESTAMP);
Return a Month:6 SQL Code:
SELECT DAY(CURRENT_TIMESTAMP);
Return a Day:22

There are many more functions available, including functions to extract milliseconds, names of the months, names of each week day, etc.
Each SQL platform varies in the actual naming of date functions. Here's a few c\The following is a list of other date functions available to most platforms of SQL with the exception of MS's SQL Server.

SQL Function Code:
SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)
SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)
SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)
SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)
SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)
SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December
SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)
SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)

Timestamps are often the easiest to work with, but we certainly are not limited to using only the current_timestamp as our parameter. We can send any date, time, or timestamp to the function which then returns our result.

SQL Code:
SELECT MONTHNAME('2004-11-27');
Return a Month Name: MONTHNAME('2004-11-27') November
Date functions can also be performed on table columns similarly to numeric and mathematical functions such as SUM() or AVG().

SQL Code:
SELECT DAYOFYEAR(column_name) FROM table_name WHERE name = 'Joe';
SQL will return a numeric result from 1 - 365 representing the day of the year that Joe's record was created/inserted.We can expand this concept one step further with the use of a subquery. Say we have a table with a column named timestamp. In this table column are timestamps of when each record was entered and we would like to know what was the numeric day of the year that a record was entered.

SQL Code:
SELECT DAYOFYEAR((SELECT DATE(timestamp) FROM employees WHERE name = 'James Bond'));
Above you can see how it is possible to combine several date functions as well as a subquery to return very specific information about Mr. James Bond.
SQL - Inserting Date DataDate data exists as numbers, strings, and timestamps. Built into most platforms are several date column types such as DATE or TIMESTAMP. By setting the default value to the current date or timestamp, the table column will automatically be filled with a current date/timestamp as each record is inserted.
Here's the code to add a timestamp column to an existing table.
SQL Code:
ALTER TABLE `orders` ADD `order_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
Now each time an order is placed in our make believe business, a timestamp of that order is also recorded.A date or timestamp table column will only allow date data types to be inserted as values so be sure to convert any strings or numbers to dates and timestamps before trying to insert them.

SQL - Datepart()
Microsoft's SQL Server takes a little different approach to working with dates. It is still possible to extract individual parts of a timestamp and several other functions also work as outlined in SQL - Date. The difference is that SQL Server uses one main function as oppose to several different functions ie the DATEPART() function.
Datepart() requires two parameters, a part argument and a date argument. By part we mean year, day of the year, day of the week, etc. Let's look at an example.

SQL Code:
SELECT DATEPART(week, '2005-12-31');Return the Week Number:53

Here we have successfully pulled the "week number" from existing date.
SQL's CURRENT_DATE function could be also be substituted or a string value representing the year ('Dec 31, 2005').