T-SQL Date Format with Convert

In this article we are going to see a very handy SQL Server T-SQL function for formatting date called Convert(). It is generally used to show date values in different styles or formats.

Normally any date value when entered in a SQL Server table gets displayed in the default format as YYYY-MM-DD. Many a times, in your client application you may need to change the output of date & time for display purpose. For doing this the T-SQL Convert function is used.

Convert Function Example

Let us create a sample database Convert_Demo in our SQL Server database instance.

Use Master
go
Create Database Convert_Demo
go
Use Convert_Demo
go
CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE date,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);
go
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);
go

Now let us see the default presentation style of the hiredate column by executing the following SQL statement.

select ename, hiredate from emp

Image1-Default-date-format

Syntax of Convert function

CONVERT(data_type(length),data_to_be_converted,style)

Data_type(length) - varchar data type particulary for displaying dates in different format.

data_to_be_converted - The date value which needs to be converted.

Style - There are some predefined style ids like 101 for American date format, 103 in British format and so on. The style chart has been shown next –



Now let us see the employee names & hiredate in American as well as British formats using T-SQL Convert function.

select ename, hiredate as [Default Date Format],
convert(varchar, hiredate, 101) as [American],
convert(varchar, hiredate, 103) as [British] 
from emp

Image2-Changed-Date-Formats

Convert without StyleID:

Convert is also useful to convert from one data type to another – For example changing Float to Int, Number to Float or Number to Varchar.

Using Convert to change from Float to Int

SELECT CONVERT(int, 14.85)

Image3-Changing-Float-To-Int

Using Convert to change from Number to Float

SELECT CONVERT(float, 14.0989654)    

Image4-Number-to-Float

Using Convert to change from Number to Varchar

SELECT CONVERT(varchar(4), '12.3456')

Image5-Number-to-Varchar

Conclusion:

 

We have seen in this article that using T-SQL function Convert, we can display date in different formats and also can type cast one data type into another.