Sunday, January 25, 2015

SQL Server - Convert() function

Convert () function in SQL Server to convert an expression from one data type to another data type

Syntax for CONVERT function
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Convert functions is usually used with date/time data type to convert a date in different formats. Below examples will show the different formats in which we can display the date/time data type using Convert function. In the below examples we use getdate() to get the current date and time of the database server.

select convert(varchar(40), getdate(), 0)

Result:-
Jan 25 2015  9:36PM

select convert(varchar(40), getdate(), 101)

Result:-
01/25/2015

select convert(varchar(40), getdate(), 102)

Result:-
2015.01.25

select convert(varchar(40), getdate(), 103)

Result:-
25/01/2015

select convert(varchar(40), getdate(), 104)

Result:-
25.01.2015

select convert(varchar(40), getdate(), 105)

Result:-
25-01-2015

select convert(varchar(40), getdate(), 106)

Result:-
25 Jan 2015

select convert(varchar(40), getdate(), 107)

Result:-
Jan 25, 2015

select convert(varchar(40), getdate(), 108)

Result:-
21:39:21

select convert(varchar(40), getdate(), 109)

Result:-
Jan 25 2015 10:19:15:583PM

select convert(varchar(40), getdate(), 110)

Result:-
01-25-2015

select convert(varchar(40), getdate(), 111)

Result:-
2015/01/25

select convert(varchar(40), getdate(), 112)

Result:-
20150125

select convert(varchar(40), getdate(), 113)

Result:-
25 Jan 2015 23:22:49:453

select convert(varchar(40), getdate(), 114)

Result:-
23:23:09:970

select convert(varchar(40), getdate(), 120)

Result:-
2015-01-25 23:23:37

select convert(varchar(40), getdate(), 121)

Result:-
2015-01-25 23:24:07.577

select convert(varchar(40), getdate(), 126)

Result:-
2015-01-25T23:24:29.860

select convert(varchar(40), getdate(), 127)

Result:-
2015-01-25T23:24:55.037

select convert(varchar(40), getdate(), 131)

Result:-
 5/04/1436 11:25:23:483PM

From the above examples we can see that how we can use the Convert () function to display date time in different formats.

No comments:

Post a Comment