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

SQL SERVER - Display Datetime in Specific Format - SQL in Sixty Seconds #033 - Video 33-800x450 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 to display datetime in specific format.

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:

-- https://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 a 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 the next section as there are a 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 (https://blog.sqlauthority.com)

Quest

Database, SQL in Sixty Seconds, SQL Scripts, SQL Server, SQL Server Management Studio
Previous Post
SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video
Next Post
SQL SERVER – Auto Recovery File Settings in SSMS – SQL in Sixty Seconds #034 – Video

Related Posts

12 Comments. Leave new

  • Thank you – nice explanation

    Reply
  • Ahmadreza Atighechi
    May 14, 2013 11:07 am

    I’m afraid in order to show millisecond we should use fff rather than mmm

    Reply
  • SELECT FORMAT ( GETDATE(), ‘dd mon yyyy HH:m:ss:mmm’, ‘en-US’ ) AS DateConvert;

    Typo error.

    Reply
  • 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?

    Reply
  • 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

    Reply
  • got the answer.
    SELECT (CONVERT(VARCHAR(30),it.EndDate,101) +’ ‘ +
    CONVERT(VARCHAR(30),it.EndDate,8)) AS end

    Reply
  • 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

    Reply
    • 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

      Reply
  • Balvant Ramani
    May 6, 2014 11:59 am

    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.

    Reply
  • SELECT CONVERT(DATE,STUFF(’25-02-14′,7,0,’20’), 105) AS [DATEFORMAT]

    Reply
  • My table contains nvarchar(50) field which contains data like “11:30 AM “, i want to convert it to time

    Reply
  • I have an SQL related Problem in My Stored Proc Where Do I Ask by attaching a Notepad Data?

    Reply

Leave a Reply