Find records using max date but exclude time

Question

I need a query to pull records from a SQL Server table based on the max date in the InsertDTS column and the date should not be included in the output.

The InsertDTS column is defined as Datetime. I need it to pull everything with the max date, but ignore the time, since records can be loaded throughout the day. I have the query below but it is pulling only records with the most recent date and time.

SELECT
    [Payer],
    [File],
    [Data_Rows],
    [Amt_Billed],
    [Amt_Paid] 
FROM
    [Customer].[dbo].[Billing] 
WHERE
    InsertDTS = (SELECT MAX(InsertDTS) 
                 FROM [Customer].[dbo].[Billing])

I tried using a CAST in the WHERE clause, but could not get it to work.

Answer 1

Convert the DATETIME to DATE to ignore the time of day.

select [Payer]
      ,[File]
      ,[Data_Rows]
      ,[Amt_Billed]
      ,[Amt_Paid] 
      from [Customer].[dbo].[Billing] 
      where CONVERT(DATE, InsertDTS) = (select CONVERT(DATE, MAX(InsertDTS)) from [Customer].[dbo].[Billing])