SQL Server : query to get MINUTE of date in specific date

The below query returns MINUTE of certain hour of a date.

For example if date value =’2017-07-19 05:50:00.000′, it will returns 50.

  • Reasons not to use an auto-incrementing number for a primary key
  • Self Join to get employee manager name
  • DDL scripts in a transaction block takes effect even when there are errors
  • Prompt user that input is out of range, based on comparing to sql query
  • Error when Utilizing SSIS ADO.NET Connection from within C# Script Task
  • How do I calculate the last day of the month in SQL?
  • My issue is I need to get MINUTE in specified date not all the dates that exist in the table.

    Query:

    SELECT 
        DATEPART(MINUTE, ClockOut) 
    FROM
        [dbo].[Attendance] 
    WHERE
        DATEPART(HOUR, ClockOut) = '5'
    

    The query I tried to return minutes only in '2017-07-19' and it returns no record.

    SELECT 
        DATEPART(MINUTE, ClockOut) 
    FROM
        [dbo].[Attendance] 
    WHERE 
        DATEPART(HOUR, ClockOut) = '5' AND ClockOut = '2017-07-19'
    

    Sample data

    Clock Out

    2017-07-19 05:50:00.000
    2017-07-20 05:51:00.000
    2017-07-21 05:52:00.000
    

    I need to return minutes at hour 5 only on this date '2017-07-19'

    3 Solutions collect form web for “SQL Server : query to get MINUTE of date in specific date”

    You are comparing datetime to date that’s why it is not returning your desired result.

    SELECT  DATEPART(MINUTE, ClockOut)
    FROM    [dbo].[Attendance]
    WHERE   DATEPART(HOUR, ClockOut) = '5'
            AND CAST(ClockOut AS DATE) = '2017-07-19';
    

    If you use ClockOut=’2017-07-19′ it’s like write ‘2017-07-19 0:00:00’. Try to use DataPart (yyyy, Clockout)=2017 and DataPary (MM, Clockout)=7 and DataPart (dd, Clockout)=19

    Your ClockOut columns contains a DateTime (2017-07-19 12:03:42) so it’s value is never exact equal to a date (2017-07-19). Use a range instead so the Query Plan can use an potential index on Clockout. Casting to a Date or using DatePart rules this out causing longer execution times if the number of rows is large.

    SELECT DATEPART(MINUTE, ClockOut) 
    FROM   [dbo].[Attendance] 
    WHERE  DATEPART(HOUR, ClockOut) = 5 
    AND    ClockOut BETWEEN '2017-07-19' 
                        AND '2017-07-20' -- start of next day
    

    working demo

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.