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.

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.

• sql
• sql-server
• sql-server-2019

Share Improve this question Follow edited Mar 6 at 5:18 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 6 at 0:12 DonDon 22411 silver badge77 bronze badges 3

• Please tag the RDBMS you are using. – Dale K Commented Mar 6 at 0:12
• Please also provide a minimal reproducible example with sample data and desired results. – Dale K Commented Mar 6 at 0:12
• Yeah. I think that did it. I was trying to CAST. Gracias! – Don Commented Mar 6 at 0:26

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

From a performance perspective its best not to run functions against columns in your where clause because it makes the query unsargable i.e. unable to use indexes. Therefore its best to use a date window rather than a specific date e.g.

select
  [Payer]
  , [File]
  , [Data_Rows]
  , [Amt_Billed]
  , [Amt_Paid] 
from [Customer].[dbo].[Billing] 
where InsertDTS >= (
  select convert(date, max(InsertDTS))
  from [Customer].[dbo].[Billing]
)

and it works equally well with cast e.g.

select
  [Payer]
  , [File]
  , [Data_Rows]
  , [Amt_Billed]
  , [Amt_Paid] 
from [Customer].[dbo].[Billing] 
where InsertDTS >= (
  select cast(max(InsertDTS) as date)
  from [Customer].[dbo].[Billing]
)

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])