SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SQL SERVER - Get Time in Hour:Minute Format from a Datetime - Get Date Part Only from Datetime datetime2005

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

SQL SERVER - Get Time in Hour:Minute Format from a Datetime - Get Date Part Only from Datetime datetime2008

I hope the above solution is clear to you all.

Quick Video on the same subject

Here is the video discussing the same concepts.

Additionally, if you want to get current datetime you can use following functions in place of datetime variable.

CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced. GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.

Please leave a comment about Hour:Minute Format. I will be happy to read them.

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server
Next Post
SQLAuthority News – Humorous SQL Cake – Funny SQL Cake

Related Posts

162 Comments. Leave new

  • Hello Sir.
    I want to know that how to get only Hour & minute from Datetime field.
    I have to make one Window service so that i need to check Hour and minute from database.
    please help me in this

    Reply
  • select convert(varchar,DATEPART(hh,getdate())-12) + ‘ : ‘ + convert(varchar,DATEPART(N,getdate())) +’ ‘+ RIGHT(CONVERT(VARCHAR,GETDATE(),109),2)

    Reply
  • yes
    this formul select convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate())) is what i seach since 1 hour.

    Thanks very much.

    Reply
  • Do you know how add 0 before hour?
    For example 1;30 -> 01:30
    and the same thing with minute?

    thanks

    Reply
    • Use convert function with style 108. It will append it automatically

      Reply
      • Thanks you but it s a wrong way.
        I try
        SELECT (convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate()),108)) as t
        but i obtained
        9:2
        I need 09:02

      • This way is good for Hour.
        SELECT
        CAST(
        CASE WHEN LEN(DATEPART(hh,getdate())) = 1
        THEN ‘0’ + CAST( DATEPART(hh,getdate()) AS varchar)
        ELSE CAST( DATEPART(hh,getdate())AS varchar) END
        AS varchar)

        But it isn’t smart

      • You can easily do this

        SELECT convert(varchar(5),getdate(),108) as t

      • It’s work.
        Thanks you very much.

  • Hello friends
    I have a case
    Where do I get a month records from a table and the condition is that q is the time 22 pm to 6 am the following day,
    so far I have this.
    DATEPART (hh, date) = 22 and date> = ‘20110901 ‘and date <'20111001'

    But I have no results, can you please help with syntax,

    Reply
  • Hello friends
    I have a case
    Where do I get a month records from a table and the condition is that the hours are from 22 pm to 6 am the following day,
    so far I have this.
    DATEPART (hh, date) = 22 and date> = ‘20110901 ‘and date <'20111001'

    But I have no results, can you please help with syntax,

    Reply
  • I want to get the Date and the hour part.
    To get the date i use select convert(varchar(10),getdate(),111) which gives me
    2011/11/25 as Output.
    But i want the hour part as well something like this:
    2011/11/25 11 AM
    Here 11 Am is the Hour part.How cn i achieve this?

    Reply
  • Select Convert(varchar(10),getdate(),101)
    union all
    select GETDATE()

    result :
    2011-12-05 00:00:00.000
    2011-12-05 07:08:38.853

    Required :
    2011-12-05
    2011-12-05 07:08:38.853

    Can you please help me to solve the problem.

    Reply
  • Hi all

    I have checkin date as varchar(10)
    as well as checkout date as varchar(10)

    and i have got all the record in the table between this dates including this date field

    so what i do

    Reply
    • You need to convert these two varchars into datetime before doing any comparison

      Reply
      • but i got the same result

        using the query like this

        Where checkinDate>=’12/12/2011′ And checkoutDate,=’12/31/2012′

  • Sharon Ferguson
    March 29, 2012 4:26 pm

    Hi,

    This is a neat site for information. Can anybody help us with the following: I’m trying to get the time part of a DateTime then convert it to minutes.
    Eg. DateTime – 2012-03-20 07:30:00.000
    Minutes – 450

    What would be the most efficient way of getting the minutes in sql?

    Thanks in advance
    Sharon

    Reply
  • Any Idea how to display Date & Time in following format:

    “02/28/2012 12:59 AM ET”

    ??

    Reply
  • I am using the exact same way to display date only but it shows in our application dashboard with full DateTime stamp in data grid..

    i typed in SQL server 2005,
    SELECT CONVERT(VARCHAR(10),GETDATE(),101) as Date

    Any idea????

    Reply
  • sequin chapman
    May 31, 2012 11:59 am

    Pinal,

    Once again you have come through with the goods. You are the man.

    Reply
  • Good solution…Thanx Pinal

    Reply
  • john_279@hotmail.com
    July 19, 2012 9:56 pm

    This is a BAD solution – converting to a string and hoping to accurately lop off the time portion with a substring, then converting it back, is a HACK.

    Reply
  • To get the response like 3:16PM

    SELECT RIGHT(LTRIM(RIGHT(Convert(varchar,GETDATE(),100),8)),8) AS ONLY_TIME

    Reply
  • I have a table with a datetime field, and I would like to find just records from today, after 10:15am.

    I have :

    where TIME_STAMP >= cast(floor(cast(GETDATE() as float)) as datetime)
    and LEFT(CONVERT(TIME(2),TIME_STAMP) ,5) > ’10:15′

    which works fine in SQL Server 10, but our prod database is SQL Server 9.

    Any ideas on an efficient way to do this?

    Reply
  • Laxman Mankala
    August 23, 2012 4:23 pm

    SELECT CONVERT(VARCHAR(19), GETDATE(), 1)
    + ‘ ‘ +
    RIGHT(CONVERT(VARCHAR(17), GETDATE(), 9),5)
    –REPLACE(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),RIGHT(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),2),”)
    +’ ‘+
    RIGHT(CONVERT(VARCHAR(19), GETDATE(), 100),2)AS ‘Date’

    Result will be : 08/23/12 4:16 PM

    Reply
  • i want 10-OCT-2012 10:50:10 PM

    Reply
  • Dear All,

    Can any one help me,

    i have two columns of one table as @ABC as DateTime and @xyz as DateTime – Datatype

    i want to subtract only time and i am trying like this

    for ABC = 21/02/2012 6:10:00 PM and XYZ = 01/01/2001 12:00:00 AM -> 1st Row

    CONVERT(varchar(10), dbo.checkingtime.ABC – dbo.checkingtime.XYZ, 108)

    and i am getting the result as 18:10 but i want the result as 05:50 in Hours and Minutes only

    is it possible ? ? ?

    Awaiting from the HelpingNature People

    Reply

Leave a Reply

Menu