Extract Hour and Minute from a Date in SQL Server

Newbie database developers often spend most of their time struggling with date and time functions in SQL Server. One common requirement is extracting values out of a date like the hour and minute.

This could be done in multiple ways and differently depending on the SQL Server edition you are using

For example

in SQL Server 2005/2008

SELECT GETDATE() as "Date"
GO
SELECT CONVERT(VARCHAR(5), GETDATE(), 114) as "Hour:Minute";
GO

returns


The same in SQL Server 2012 can be done using the new FORMAT functions

SELECT FORMAT(GETDATE() , 'hh:mm') as “Hour:Minute”;

Similarly as explained by Madhivanan in this post, there are several ways to format dates in various formats using the new SQL Server 2012 FORMAT functions

**Query 1**:

    SELECT FORMAT(GETDATE(),'dd-MM-yyyy') AS [dd_mm_yyyy]

**[Results]**:
    
    | DD_MM_YYYY |
    |------------|
    | 19-09-2013 |


**Query 2**:

    SELECT FORMAT(GETDATE(),'MM/dd/yyyy') AS [mm/dd/yyyy]

**[Results]**:
    
    | MM/DD/YYYY |
    |------------|
    | 09/19/2013 |


**Query 3**:

    SELECT FORMAT(GETDATE(),'MMM-yyyy') AS [MMM-yyyy]

**[Results]**:
    
    | MMM-YYYY |
    |----------|
    | Sep-2013 |


**Query 4**:

    SELECT FORMAT(GETDATE(),'MMM dd,yyyy') AS [MMM dd,yyyy]

**[Results]**:
    
    | MMM DD,YYYY |
    |-------------|
    | Sep 19,2013 |


**Query 5**:

    SELECT FORMAT(GETDATE(),'HH:mm:ss') AS [HH:mm:ss]

**[Results]**:
    
    | HH:MM:SS |
    |----------|
    | 10:02:25 |


**Query 6**:

    SELECT FORMAT(GETDATE(),'dddd, dd MMMM yyyy') AS [long format]

**[Results]**:
    
    |                 LONG FORMAT |
    |-----------------------------|
    | Thursday, 19 September 2013 |

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook