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.

Definition for Convert Function:


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.


Differences between Convert and Cast :


Convert function is used in Sql Server but Cast is more generic(can be used in other dbms). Convert provides more flexability than 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))

In cast function we have one Style of date format. But in Convert function we have many convert which are shown below.


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:

The CONVERT() function can be used to modify how the standard datetime format is presented to end users in a query or report.


Comments

Author: maruthanayakam21 Feb 2014 Member Level: Silver   Points : 1

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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: