how to pull current day and time, without changing the date SQL

Question

Could someone advise me how to change this so it always pulls the current day?

SELECT Groups.GroupName, AgentTeams.TeamName, [Agent.Firstname] & [ Agent.Lastname, CRC.Description, Count(History.HistoryID) AS [CRC Count]
FROM ((GroupAgent INNER JOIN Groups ON GroupAgent.GroupID = Groups.GroupID) INNER JOIN ((Agent LEFT JOIN History ON Agent.AgentID = History.AgentID) LEFT JOIN CRC ON History.CRC = CRC.CRC) ON  GroupAgent.AgentID = Agent.AgentID) INNER JOIN (AgentTeams INNER JOIN AgentStartDates ON  AgentTeams.TeamID = AgentStartDates.TeamID) ON GroupAgent.AgentID = AgentStartDates.AgentID
WHERE (((History.CallDateTime) Between CONVERT(Datetime,'20/11/2014 00:00:01',105) And CONVERT(Datetime,'20/11/2014 23:59:01',105))) 
GROUP BY Groups.GroupName, AgentTeams.TeamName, Agent.Firstname, Agent.Lastname, CRC.Description
ORDER BY Groups.GroupName, AgentTeams.TeamName;

score 0 · Accepted Answer · answered Nov 20 '14 at 14:20

Assuming you mean History.CallDateTime is the current day:

WHERE History.CallDateTime >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
    AND History.CallDateTime < CAST(CAST(DATEADD(dd,1,GETDATE()) AS DATE) AS DATETIME)

If you're on SQL Server 2005 or earlier, you'll need to use:

WHERE History.CallDateTime >= DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
    AND History.CallDateTime < DATEADD(dd, DATEDIFF(dd, 0, getdate()) + 1, 0)

how to pull current day and time, without changing the date SQL

1 Answers1