SQL FORMAT Function

[earrapee]

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.