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.
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.
