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

  • Hi
    I want to convert ‘2013-01-15 11:23:49.930′

    output =’2013-01-15 11:00:00.000’
    Please help.

    Reply
  • Hi ALL,
    i want to convert dateformat ‘2013-01-15 11:23:34.930’ into 2013-01-15 11:00:00.000 this format please help.

    Reply
  • sir how to find sum (datetime) for group by operation

    Reply
  • I wrote a small function which returns you table having Time Diffence as on of its attribute. Function is as follow:-

    CREATE FUNCTION dbo.TimeDiff (@STARTDATE datetime,@ENDDATE datetime)

    RETURNS @TIMEDIFF TABLE (ID int IDENTITY(1, 1) NOT NULL, TimeDiff TIME NULL) AS

    BEGIN

    DECLARE @24DATE DATETIME
    SET @24DATE = ’23:59:59.000′

    IF (@STARTDATE > @ENDDATE)
    INSERT INTO @TIMEDIFF
    SELECT DATEADD (SECOND,1, CONVERT(TIME(0), ( @24DATE -(@ENDDATE – @STARTDATE))))
    ELSE
    INSERT INTO @TIMEDIFF
    SELECT CONVERT(TIME(0), ( (@ENDDATE – @STARTDATE)))

    RETURN
    END

    GO

    Reply
  • I wanted to be able to format dates in a similar way to the VB6 FORMAT() function where you give it a date and a picture of the date and it returns a string in that format.
    I have written this as a SQL function and use it quite often

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(‘Format’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION Format
    GO

    Create Function Format(@Date DateTime,@Fmt Char(30)) Returns Char(30) as
    Begin
    — @Fmt can be dd/mm/yyyy, yyyy-mm-dd, dd-mmm-yyyy, dd/mm/yy, ddd dd/mm/yyyy, dddd dd-mmmm-yyyy, dddd mmmm dd, yyyy etc
    — @Fmt can also include HH:mm, HH:mm:ss, HH:mm:ss:ttt formats – always in 24 hour format at this stage
    — FD is First Day-of-month, LD is Last Day-of-month, FYYY is financial year, FM is month in financial year, MN is minutes of hour

    Select @Fmt=Replace(@Fmt,’FD’,’01’);
    Select @Fmt=Replace(@Fmt,’LD’,right(rtrim(cast(100+Day(DateAdd(Day,-1,DateAdd(Month,1,DateAdd(Day,1-Day(@Date),@Date)))) as Char(3))),2));
    Select @Fmt=Replace(@Fmt,’FM’,Right(RTrim(Cast(100+Case When Month(@Date)>6 then Month(@Date)-6 else Month(@Date)+6 End as char(3))),2));
    Select @Fmt=Replace(@Fmt,’FYYY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),4));
    Select @Fmt=Replace(@Fmt,’FY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),2));
    Select @Fmt=Replace(@Fmt,’HH’,left(Cast(Cast(@Date as Time) as char(10)),2));
    Select @Fmt=Replace(@Fmt,’:MM’,SubString(Cast(Cast(@Date as Time) as char(12)),3,3));
    Select @Fmt=Replace(@Fmt,’MN’,SubString(Cast(Cast(@Date as Time) as char(12)),4,2));
    Select @Fmt=Replace(@Fmt,’SS’,SubString(Cast(Cast(@Date as Time) as char(12)),7,2));
    Select @Fmt=Replace(@Fmt,’TTT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,3));
    Select @Fmt=Replace(@Fmt,’TT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,2));
    Select @Fmt=Replace(@Fmt,’DDDD’,DATENAME(dw,@Date));
    Select @Fmt=Replace(@Fmt,’DDD’,Left(DATENAME(dw,@Date),3));
    Select @Fmt=Replace(@Fmt,’DD’,Right(Rtrim(cast(100+day(@Date) as Char(3))),2));
    Select @Fmt=Replace(@Fmt,’MMMM’,DATENAME(month,@Date));
    Select @Fmt=Replace(@Fmt,’MMM’,Left(DATENAME(month,@Date),3));
    Select @Fmt=Replace(@Fmt,’MM’,Right(Rtrim(cast(100+month(@Date) as Char(3))),2));
    Select @Fmt=Replace(@Fmt,’YYYY’,Right(Rtrim(cast(10000+Year(@Date) as Char(5))),4));
    Select @Fmt=Replace(@Fmt,’YY’,Right(Rtrim(cast(10000+Year(@Date) as Char(6))),2));
    Return ltrim(rtrim(@Fmt));
    End
    GO
    SELECT getdate(),
    dbo.Format(getDate(),’ddd dd-mmm-yyyy HH:mm:ss.ttt’),
    dbo.Format(getDate(),’HH:mn’),
    dbo.Format(getDate(),’fd-mmm-yyyy’),
    dbo.Format(getDate(),’LD-mmm-yyyy’),
    dbo.Format(GetDate(),’FYYY-FM’);

    Reply
  • I need to get the number of hours from two date formats.. i.e i need to get number of hours spent by a staff on a project i have decidded to use start time and end time.. solution? :).

    Reply
  • 03:07:30.8570000 i got this as time and what is this “8570000” here…??

    Reply
  • how i can convert time into datatime which returns today’s date and time from dB.

    let’s say i have time only in MY DB. 12:30 PM, when i use CONVERT(DATETIME, a.StartTime), it always return 1900-01-01 12:30:00.000. is there any way i can get today’s date.

    like 2013-07-11 12:30:00:000

    Reply
    • This is one of the methods

      select dateadd(day,datediff(day,0,getdate()),CONVERT(DATETIME, ’12:30 PM’));

      Reply
  • Hey, I am looking for sql statement to get number of minutes till to date in that year. Any help?

    Reply
  • طراحی سایت
    September 18, 2013 2:21 am

    Fabulous, what a webpage it is! This website gives valuable information
    to us, keep it up.

    Reply
  • hi all,
    wt is the difference between CAST() and CONVERT()

    Reply
    • Both are same however CONVERT has advantage over CAST in terms of formatting date values with more options

      Reply
  • Hi all,

    Thanks to author for the great post!

    I was wondering what the query would be if I wanted to extract date + hours + minutes out of datetime. I’m looking to get rid of the seconds.

    Thanks very much for the help,

    Tomas

    Reply
  • when I use this command it returns the date , and the time of the time i run the query and not the recorded date and time in the date time field in the table?!

    Reply
  • Hi, I’ve tryed to use this but it doesn’t function, it appears a message saying: “Type TIME is not a defined system type.”, also if I use the date, I need to compare with the BETWEEN in hours and it doesn’t function, but if I put the date (for example “BETWEEN ’04/04/2015′ AND ’04/05/2015′ “) the query give a result, but if I use “BETWEEN ’06:00:00.000′ AND ’20:00:00.000′ ” it doesn’t show any result, can anyone help please?

    Reply
  • TO GET CURRENT TIME IN SQL

    SELECT TO_CHAR(SYSDATE,’HH24:MI:SS’) FROM DUAL;

    Reply
  • how to split date month year in three columns

    Reply
  • Manish Srivastava
    August 9, 2017 10:04 pm

    Hi I wanted to convert DateTime to CET timeZone

    Reply
  • how to change 01:01:00 to 1hr 01 min 00sec

    Reply
  • Hi, can you please tell how to convert ‘2017-12-04 14:30:14.410’ to ‘2017-12-04 14:30’.
    I want only date and hh:mm
    Can you please help

    Reply

Leave a Reply

Menu