SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video

A very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes. In SQL Server there are various functions to aid this requirement. There is function CAST, which developers have been using for a long time as well function CONVERT which is a more enhanced version of CAST. In the latest version of SQL Server 2012 a new function FORMAT is introduced as well.

In this SQL in Sixty Seconds video we cover two different methods to display the datetime in specific format. 1) CONVERT function and 2) FORMAT function.

Let me know what you think of this video. Here is the script which is used in the video:

-- http://blog.SQLAuthority.com
-- SQL Server 2000/2005/2008/2012 onwards
-- Datetime
SELECT CONVERT(VARCHAR(30),GETDATE()) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),10) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),5) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
GO
-- SQL Server 2012 onwards
-- Various format of Datetime
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'dd mon yyyy HH:m:ss:mmm', 'en-US' ) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'HH:m:ss:mmm', 'en-US' ) AS DateConvert;
GO
-- Specific usage of Format function
SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;

This video discusses CONVERT and FORMAT in simple manner but the subject is much deeper and there are lots of information to cover along with it. I strongly suggest that you go over related blog posts in next section as there are wealth of knowledge discussed there.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video

  1. Hi,
    (SELECT CONVERT(VARCHAR(30),it.StartDate) )[start] will return date as Jun 17 2013 2:00PM and ( SELECT CONVERT(VARCHAR(30),it.EndDate,101))[end] will return date as 06/17/2013 but i need date format like 06/17/2013 2:00 PM. Can you please help me in this?

  2. Hi,
    ( SELECT CONVERT(VARCHAR(30),it.EndDate,101))[end] will return date as 06/17/2013 and (SELECT CONVERT(VARCHAR(30),it.StartDate) )[start] will return date as Jun 17 2013 2:00PM and i need date in the following format 06/17/2013 2:00 PM(“/” is important) . Can you please help me on this

  3. Hi pinal

    i want to have a date range list in a drop down for reports like as follows
    Today
    Yesterday
    Before Two Days
    Current Week
    Last Week
    Current Month
    Last Month
    Last 3 Months
    Current Year
    Last Year
    First Quater
    Second Quater
    Third Quater
    Fourth Quater

    • Hi Pinal

      ITS VERY URGENT

      I want to convert ’25-02-14′ (dd-mm-yy) format to ‘2014-02-25′ (yyyy-mm-dd).

      I’m trying of many methods but I could not able to get through.

      Waiting for your reply.

      Thanks & Regards
      Madhu.R

  4. My table has filed with data type datetime…. when i update it it gives conversion error .. i update it via procedure.. i pass parameter to procedure.. and update table.. pls give solution.

Leave a Reply

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