Post reply

selecting only hour and minute from datetime.

  • December 9, 2011 at 7:44 am

    #391088

    How can I select only Hour and Minute from datetime without any seconds part.

    I need only 09:43 from ' 2011-12-09 09:43:36.743 '

  • December 9, 2011 at 7:47 am

    #1419381

    WangcChiKaBastar (12/9/2011)

    How can I select only Hour and Minute from datetime without any seconds part.

    I need only 09:43 from ' 2011-12-09 09:43:36.743 '

    SELECT CONVERT(TIME,'2011-12-09 09:43:36.743')

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 9, 2011 at 7:51 am

    #1419383

    Thanks, I think I need the date as well.

    so I will need '2011-12-09 09:50' from 2011-12-09 09:50:34.600

    in SQL Server 2005.

  • December 9, 2011 at 7:58 am

    #1419389

    select {fn extract(hour from datetimeColumnName)},datetimeColumnNamefrom [TableName]

  • December 9, 2011 at 8:06 am

    #1419395

    WangcChiKaBastar (12/9/2011)

    Thanks, I think I need the date as well.

    so I will need '2011-12-09 09:50' from 2011-12-09 09:50:34.600

    in SQL Server 2005.

    You posted in the SQL Server 2008 forum.

    DECLARE @DATE AS DATETIME

    SET @DATE = '2011-12-09 09:50:34.600'

    SELECT CONVERT(VARCHAR(11),DATEADD(dd, DATEDIFF(dd, 0, @DATE), 0), 20) + CONVERT(VARCHAR(5),DATEADD(dd, -DATEDIFF(dd, 0, @DATE), @DATE),14)

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 9, 2011 at 8:15 am

    #1419407

    this trick of adding the # minutes to the minimum sql date will trucate to the last minute that occurred:

    select DATEADD(minute, DATEDIFF(minute,0,getdate()), 0)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 9, 2011 at 8:15 am

    #1419410

    The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes, 5 Minutes, x number of Minutes, Minute, or Second.

    Start of Time Period Functions:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

    Start of Week Function:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

    select

    a.DT,

    DT__To_Minute = dateadd(ms,-(datepart(ss,a.DT)*1000)-datepart(ms,a.DT),a.DT)

    from

    ( -- Test Data

    select DT = convert(datetime,'2011-12-09 09:43:36.743') union all

    select DT = getdate()

    ) a

    order by

    a.DT

    Results:

    DT DT__To_Minute

    ----------------------- -----------------------

    2009-11-12 09:43:36.743 2009-11-12 09:43:00.000

    2011-12-09 10:06:35.547 2011-12-09 10:06:00.000

    (2 row(s) affected)

  • December 9, 2011 at 8:25 am

    #1419418

    Lowell (12/9/2011)

    this trick of adding the # minutes to the minimum sql date will trucate to the last minute that occurred:

    select DATEADD(minute, DATEDIFF(minute,0,getdate()), 0)

    Your code works, provided the date is within a limit that should be OK for most applications. The code I posted works with all datetime values.

    -- Works OK

    select DT = DATEADD(minute, DATEDIFF(minute,0,'5983-01-01 00:00:36.743'), 0)

    -- Causes Overflow

    select DT = DATEADD(minute, DATEDIFF(minute,0,'5984-01-01 00:00:36.743'), 0)

  • December 9, 2011 at 8:27 am

    #1419420

    select {fn extract(hour from datetimeColumnName)} +':'+{fn extract(minute from datetimeColumnName)} ,datetimeColumnNamefrom [TableName]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply