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