SQL Date Functions

[chili5]

SQL Date Functions

One thing you can do with dates is use the UNIX_TIMESTAMP function and the BETWEEN clause to determine if a date lies between two dates. However, there is a lot more that you can do.


Getting Current Date and Time

SQL provides us with a function NOW that returns the date and time.

Example:

SELECT NOW();

Output:

2009-09-04 06:25:27

You could easily store this value in a datetime field. You might use this in a registration script to record the date and time that someone registered on a website.


Getting Current Date

You might just be interested in the current date. SQL provides us with a function CURDATE that returns the current date.

Example:

SELECT CURDATE();

Output:

2009-09-04

Getting Current Time

The function CURTIME returns the current time. However, this might not be as accurate as you want because you are using the time on the database server. If getting the exact time the user did something is important, then the program should get the time value and pass that to the database.

Example:

SELECT CURTIME();

Output:

06:31:59

Adding Days

One thing that is really interesting about dates, is we can do all sorts of math to manipulate them. Consider the ADDDATE function which takes a date and adds x days to it.

Example:

SELECT ADDDATE(CURDATE(),30);

This code is going to add 30 days to the current date.

Output:

2009-10-04

You might use these functions in correlation with the IF function to determine if the user is allowed to do something. Say you only want people to post to your bulletin board if they have been registered for 30 days. Granted, this is something that the application should do. Not, something the database should do, but the database could do this for you.

Example:

SELECT IF(UNIX_TIMESTAMP('2009-04-32')>=UNIX_TIMESTAMP(ADDDATE('2005-04-32',30)),"yes","no");

If the date we passed in is greater than or equal to the registration date + 30 dates then the user is allowed to do something, and we return "yes". Otherwise, we return no.

Output:

No

Date Diff - Subtracting Dates

We can use the DATEDIFF function to subtract two dates. This function returns the number of days between the two dates.

Example:

SELECT DATEDIFF(NOW(),ADDDATE(NOW(),30));

I chose this example because it is easy to determine what the result is. Since I am finding the number of days between two dates, I chose two dates that are 30 days apart.

This SQL function is going to evaluate:

NOW()-ADDDATE(NOW(),30));

Thus the result should be -30. Now, let us run our code and see what happens.

Output:

-30

Formatting Dates

This function is a monster, it is very similar to the date function in PHP and works much in the same way.

The function prototype is:

DATE_FORMAT(date,format)

The format parameter is made up of different specifies that indicate how you want the date to be displayed.

Example:

SELECT DATE_FORMAT(NOW(),'%W %M %e, %Y %T');

This function is going to return the string formatted with a date and a time.

Output:

Friday September 4, 2009 06:50:01

Instead of memorizing all the specificiers, you are going to save a lot more time if you just look them up as you need them.

Make Time Function

This function returns a time value that is calculated from the parameters. It accepts three parameters, hour, minute and second.

Example:

SELECT MAKETIME(9,30,05);

Output:

9:30:05

Formatting Time Values

There is a function TIME_FORMAT that works much like DATE_FORMAT. It takes two parameters time and format. However the only format specifies you can use are ones for hours, minutes, seconds and microseconds. If you use any other specifies the return value will either be NULL or 0.

Example:

SELECT TIME_FORMAT(CURTIME(),'%I:%i:%S %p');

Output:

07:06:24 AM

[WingedPanther]

One thing to be acutely aware of is that different databases use different functions for date/string handling. MS SQL uses GETDATE(), for example, and DATEADD() take three parameters!

+rep

[Jordan]

Very handy functions. +rep!

[DarkLordoftheMonkeys]

I just learned some new stuff. +rep.

[technica]

A article with good example always help reader to understand the concept. This is what I got it from here. Good work mate.