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)

, , ,
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

  • Thanks man for the great blog it is really so informative .
    But i guess the code for SQL 2000/2005 is missing the year part so

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

    Should be

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

    Regards,
    Ziek

    Reply
  • SELECT
    CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond

    Is the following code returning the time of the server in military format?

    Reply
  • Is there any better way to display HHMM in SQL 2005?

    SELECT substring(convert (nchar(5), getdate(),14),1,2) + substring(convert (nchar(5), getdate(),14),4,2)

    Thanks,
    Raj

    Reply
  • Valentin Petkov
    July 20, 2010 11:46 pm

    short cut for SQL 2008

    select CAST(GETDATE() as date) as DateOnly

    Reply
  • declared arrivaltime varchar
    declared completedtime varchar
    values is 10:00 and 11:00
    i want to find difference between the hours

    thanks in advance

    plz send to mail if possible

    Reply
    • select datediff(hour,cast(arrivaltime as datetime),cast(completedtime as datetime)) from your_table

      Reply
      • I have a similar requirement but I need the difference of minutes also.
        That is, I have startTime as varchar
        endTime as varchar

        Values are 10:00 AM and 7:30 PM
        I need to find the difference between these two times. How to do that? I tried the above said query but that gives only the difference in hours. I need the time also. In this case I need 09:30 as result.

        Please help. Thanks

      • Try this

        declare @startTime varchar(10),@endTime varchar(10)
        select @startTime =’10:00 AM’, @endtime =’7:30 PM’
        select convert(varchar(10),@endtime-cast(@starttime as datetime),108)

    • Dim FTime = DateTime.Parse(FromTime)
      Dim TTime = DateTime.Parse(ToTime)
      Dim TimeDiffetent = TTime – FTime
      txtDifferent = TimeDifferent

      Reply
  • Hi All,
    how to maintainance beter SQL system, when we have a lot of transactions, how to make sure that it’s run ok when we have about 500 000 rows transactions a day ?

    Reply
  • Alex Koranteng
    October 3, 2010 6:55 pm

    Pinal,

    Great tip for handling only date portion, but I find the following to display date in YYYY-MM-DD format and will allowing sorting for this value

    SELECT
    CONVERT(VARCHAR(10),GETDATE(),120) AS DateOnly
    GO

    Reply
  • hello all,

    how can i retrieve only the hour?

    Reply
  • hello all,

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    alter PROCEDURE dbo.[GetClassInfo]
    @TimeNow int,
    @DayId nvarchar(MAX)

    AS
    BEGIN

    SELECT TeacherId,ClassName from ClassInfo where
    TimeId=@TimeNow and DayId=@DayId
    END
    GO

    timenow has a hour and so i want timeid to be hour as well how do i go about converting timeid to just an integer hour value?

    Reply
  • thanks in advance

    Reply
  • Hello Frnds,

    Please Try this may be its solve your problem:-

    select substring(CONVERT(varchar,getdate(),100),12,LEN(CONVERT(varchar,getdate(),100)))

    Output of this statement is ” 5:32PM ”

    Thank you

    Reply
  • Hello friends,
    i have a scenario like below:

    Need to extract data from a table from previous day 08:30 AM to current day 08:29 AM.

    Thanks in advance.

    Reply
    • Where
      datecol>=dateadd(day,datediff(day,0,getdate()),’8:30′)-1 and
      datecol<dateadd(day,datediff(day,0,getdate()),'8:30')+1

      Reply
  • CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
    is cutting off the year part.

    i think it should be:
    CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly

    Reply
  • Pinal, how would i convert a time like 4:23:23 AM into 04:23:23 AM. I mean it’s just padding zero’s if there isn’t a built in convert code. I want to use it with masked edit control of the ajax control toolkit. Stupid thing will not accept 4:23:23 AM as valid

    Reply
  • GOOD ONE.

    Reply
  • Hello there, i got time on my query 21:10:00 how to get only 20:10 ?

    many thanks

    Reply
  • Himanshu Batra
    June 30, 2011 2:13 pm

    thnks buddy

    Reply
  • Hello, I am a newbie to SQL server. How do I use the time only retrieval function to get the time from a database field e.g. I have a field called sDateTime which is a datetime field

    Reply
  • Hi Madhivanan,

    i want to calculate how much time spending in office
    based on logintime and logout time,
    condition is :9 hours perday
    i want to display spending time ,reminingtime

    plz help me

    Reply
    • Hi Pinal,

      Plz help me

      Reply
    • Post some sample data and expected result

      Reply
      • DECLARE @Logintime VARCHAR(36)
        set @Logintime=’11:35am’
        DECLARE @Logouttime VARCHAR(10)
        DECLARE @diff VARCHAR(36),

        @hr VARCHAR(30),
        @min VARCHAR(2),
        @sec VARCHAR(30),
        @resttime varchar(36)
        ,@rtime VARCHAR(30)
        DECLARE @NewLineChar AS CHAR(2)
        SET @NewLineChar= CHAR(13) + CHAR(10)
        SET @Logouttime = (SELECT right(Getdate(), 8)AS t)
        SET @sec = Abs(Datediff(SECOND, @Logintime, @Logouttime))
        SET @hr = @sec / 3600
        SET @min = ( @sec – ( @hr * 3600 ) ) / 60
        SET @sec = ( @sec – ( @hr * 3600 ) – ( @min * 60 ) )

        IF CAST(@hr AS INT) < 10
        SET @hr = '0' + @hr

        IF CAST(@min AS INT) < 10
        SET @min = '0' + @min

        IF CAST(@sec AS INT) < 10
        SET @sec = '0' + @sec

        SET @diff = @hr + ':' + @min + ':' + @sec
        print @diff

        PRINT 'YOU ARE SPENDING IN OFFICE :'+' ' + @diff + ' ' + 'HOURS'

        IF CAST(@hr AS INT) < 9
        print 'YOU WILL GET LOSS OF PAY'

        ELSE
        PRINT 'SUCCESSFULLY CONSIDERED YOUR ATTENDENCE FOR THIS DAY '

  • Hi madhivanan

    This is my query output is
    08:16:00
    YOU ARE SPENDING IN OFFICE : 08:16:00 HOURS
    YOU WILL GET LOSS OF PAY
    Wait For Rest of Time:

    I want to display Rest of Time

    Reply

Leave a Reply