Date Convert function in SQL Server 2008.
In this article I will Explain date convert functions which are available in SQL Server 2008 and its definition then difference between Convert funtion and cast function in sql server.Syntax and Example for Convert and Cast function.Different Styles of date format by using Convert function.Advantages of using Convert Function in Sql Server.
The Convert function which converts one data type to other type. By using convert function we can get different types of date format in SQL Server. Convert function is used in Sql Server but Cast is more generic(can be used in other dbms). Convert provides more flexability than Cast. In cast function we have one Style of date format. But in Convert function we have many convert which are shown below. The CONVERT() function can be used to modify how the standard datetime format is presented to end users in a query or report. Definition for Convert Function:
Differences between Convert and Cast :
Syntax for Convert Function:
Convert (data_type (length, Expression), Style)Example:
Convert(varchar(6),Agdate,112)Syntax for Cast:
CAST ( expression AS data_type [ ( length ) ] )Example:
select CAST(GETDATE() as varchar(10))Different Types of Date Formats in Convert:
Format 1: mon dd yyyy hh:miAM (or PM)
select CONVERT(VARCHAR(20),GETDATE(),100)
select CONVERT(VARCHAR(20),GETDATE(),0)
Answer: Mar 28 2013 3:56PM
Format 2: mm/dd/yyyy
select CONVERT(VARCHAR(20),GETDATE(),101)
Answer: 03/28/2013
Format 3: yy.mm.dd
select CONVERT(VARCHAR(20),GETDATE(),102)
Answer: 2013.03.28
Format 4: dd/mm/yyyy
select CONVERT(VARCHAR(20),GETDATE(),103)
Answer: 28/03/2013
Format 5: dd.mm.yy
select CONVERT(VARCHAR(20),GETDATE(),104)
Answer: 28.03.2013
Format 6: dd-mm-yy
CONVERT(VARCHAR(20),GETDATE(),105)
Answer: 28-03-2013
Format 7: dd mon yy
select CONVERT(VARCHAR(20),GETDATE(),106)
Answer: 28 Mar 2013
Format 8: Mon dd, yy
select CONVERT(VARCHAR(20),GETDATE(),107)
Answer: Mar 28, 2013
Format 9: hh:mi:ss
select CONVERT(VARCHAR(20),GETDATE(),108)
Answer: 15:53:06
Format 10: mon dd yyyy hh:mi:ss:mmmAM (or PM)
select CONVERT(VARCHAR(20),GETDATE(),9)
select CONVERT(VARCHAR(20),GETDATE(),109)
Answer: Mar 28 2013 3:52:34
Format 11:mm-dd-yy
select CONVERT(VARCHAR(20),GETDATE(),110)
Answer: 03-28-2013
Format 12: yy/mm/dd
select CONVERT(VARCHAR(20),GETDATE(),111)
Answer: 2013/03/28
Format 13: yyyymmdd
select CONVERT(VARCHAR(20),GETDATE(),112)
Answer: 20130328
Format 14: dd mon yyyy hh:mi:ss:mmm(24h)
select CONVERT(VARCHAR(20),GETDATE(),13)
select CONVERT(VARCHAR(20),GETDATE(),113)
Answer: 28 Mar 2013 15:46:26
Format 15: hh:mi:ss:mmm(24h)
select CONVERT(VARCHAR(20),GETDATE(),114)
Answer: 15:46:59:113
Format 16: yyyy-mm-dd hh:mi:ss(24h)
select CONVERT(VARCHAR(20),GETDATE(),20)
select CONVERT(VARCHAR(20),GETDATE(),120)
Answer: 2013-03-28 15:47:46
Format 17: yyyy-mm-dd hh:mi:ss.mmm(24h)
select CONVERT(VARCHAR(20),GETDATE(),121)
Answer: 2013-03-28 15:48:32.
Format 18: yyyy-mm-ddThh:mi:ss.mmm (no spaces)
select CONVERT(VARCHAR(20),GETDATE(),126)
select CONVERT(VARCHAR(20),GETDATE(),127)
Answer: 2013-03-28T15:49:11.
Advantage:
Definition and Usage
The CONVERT() function is a general function that converts an expression of one data type to another.
The CONVERT() function can be used to display date/time data in different formats.
Syntax
CO