Formatting Dates with SQL Server


By default, when you query your database many of your dates may have the following format: yyyy-mm-dd 00:00:00.000 (i.e. 2011-05-04 14:33:35.743). However you may want to format this date to look like May 4 2011 2:35PM or something that’s easier to read. To accomplish this use SQL Server’s CONVERT function. The convert function can take up to 3 arguments, the output type, the value it will be converted, and optionally the format number. Below are some examples and how to use CONVERT().

SELECT CONVERT(varchar(50), GETDATE(), 100)

will give you

May 4 2011 2:38PM


SELECT CONVERT(varchar(50), GETDATE(), 101)

will give you

05/04/2011


SELECT CONVERT(varchar(50), GETDATE(), 106)

will give you

04 May 2011

SELECT CONVERT(varchar(50), GETDATE(), 107)

will give you

May 04, 2011

Nice right? Hope it’s useful to you.

Leave a comment

Filed under SQL

Comment on this

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s