SQL FORMAT Function
Submitted by admin on Thursday, April 28, 2011 - 15:11.
The FORMAT() function is normally applied on formatting column with Date value like YYYY, MM, and DD.
SQL FORMAT() Syntax
SELECT FORMAT(column_name(s)) FROM TABLE
Consider the following table for this exercise
Users
| Firstname | Lastname | Salary | DeptID |
|---|---|---|---|
| John | Smith | 1000 | 1 |
| Mathew | Simon | 3000 | 1 |
| Bill | Steve | 2200 | 1 |
| Amanda | Rogers | 1800 | 2 |
| Steve | Hills | 2800 | 2 |
| Steve | jobs | 2400 | 2 |
| bill | cosby | 700 | 3 |
Example # 1
SELECT Salary, FORMAT(NOW(),'YYYY/MM/DD HH:MM:SS') AS TILL_date FROM users
Result of the Query
| Salary | TILL_date |
|---|---|
| 1000 | 2011/03/23 03:24:06 AM |
| 3000 | 2011/03/23 03:24:06 AM |
| 2200 | 2011/03/23 03:24:06 AM |
| 1800 | 2011/03/23 03:24:06 AM |
| 2800 | 2011/03/23 03:24:06 AM |
| 2400 | 2011/03/23 03:24:06 AM |
| 700 | 2011/03/23 03:24:06 AM |
The dates are now formatted according to the format we mention in the function, which is YYYY/MM/DD HH:MM:SS.
To exclude time all you need is to remove "HH:MM:SS".
Example # 2
SELECT Salary, FORMAT(NOW(),'YYYY/MM/DD') AS TILL_date FROM users
| Salary | TILL_date |
|---|---|
| 1000 | 2011/03/23 |
| 3000 | 2011/03/23 |
| 2200 | 2011/03/23 |
| 1800 | 2011/03/23 |
| 2800 | 2011/03/23 |
| 2400 | 2011/03/23 |
| 700 | 2011/03/23 |
As you can see above, you can also change the name of the column by using the SQL Alias.