Convert date to different date string using Convert function of ms sql server 2005

Many times we need to convert datetime to different strings representing date, varying from 2009/06/2009 to June 26, 2009, and so on. Microsoft sql server 2005 can do the job for us! I had the same need some time ago, and sql server did this for me. (By the way, don't we programmers often find it irritating to have datetime representations different in different data base providers and different programming languages? Yes. Exactly here is what I am pointing towards what sql server 2005 can do for us. Follow the rest of this article for the magic!)

This is the syntax:

CONVERT (data-type, date-time-string-input, date-display-style)

Parameters:
data-type: Any data type from nchar, nvarchar, char, varchar, binary, varbinary [length is optional, e.g. varchar(20) or varchar(22)
date-time-string-input: Date time string supplied as input (You can use GETDATE() function etc.)
date-display-style: The string style (You need '2009.06.26' or 'June 26, 2009' etc. You can even use the useful style number. Read on.)

Note: If you provide insufficient length, you may be trimming your result!

Now, here is a list of the sql query and the result to the query that will introduce a number of the ways to get desired date strings.

SELECT CONVERT(VARCHAR(20),GETDATE(),100) 
Result: Jun 26 2009 5:53PM

SELECT CONVERT(VARCHAR(20),GETDATE(),101) 
Result: 06/26/2009

SELECT CONVERT(VARCHAR(20),GETDATE(),102) 
Result: 2009.06.26

SELECT CONVERT(VARCHAR(20),GETDATE(),103) 
Result: 26/06/2009

SELECT CONVERT(VARCHAR(20),GETDATE(),104) 
Result: 26.06.2009

SELECT CONVERT(VARCHAR(20),GETDATE(),105) 
Result: 26-06-2009

SELECT CONVERT(VARCHAR(20),GETDATE(),106) 
Result: 26 Jun 2009

SELECT CONVERT(VARCHAR(20),GETDATE(),107) 
Result: Jun 26, 2009

SELECT CONVERT(VARCHAR(20),GETDATE(),108) 
Result: 17:49:42

SELECT CONVERT(VARCHAR(20),GETDATE(),109) 
Result: Jun 26 2009 5:50:02

SELECT CONVERT(VARCHAR(20),GETDATE(),110) 
Result: 06-26-2009

SELECT CONVERT(VARCHAR(20),GETDATE(),111) 
Result: 2009/06/26

SELECT CONVERT(VARCHAR(20),GETDATE(),113) 
Result: 26 Jun 2009 17:51:24

SELECT CONVERT(VARCHAR(20),GETDATE(),114) 
Result: 17:51:44:397

SELECT CONVERT(VARCHAR(20),GETDATE(),120) 
Result: 2009-06-26 18:00:17

SELECT CONVERT(VARCHAR(20),GETDATE(),121) 
Result: 2009-06-26 18:01:14

SELECT CONVERT(VARCHAR(20),GETDATE(),126) 
Result: 2009-06-26T18:02:00

As you can see, I have used the select statement to retrieve the desired sql query result. You can use it with any combination of sql query to perform any of the CRUD (Create, Read, Update and Delete) operations.

You can comment to add to the above list. It may be incomplete. Remember your participation is very important to make it more complete and more useful. You are as always heartily welcome to post you suggestions, comments, rate this article or spread the words by adding it to the social bookmarks! Happy Programming!