How to SELECT the MIN of a Batch ID for a given date

[RunTime91]

Hello everyone ~

I need to construct a WHERE statement that allows me to select only the records that uploaded at the earliest run time each morning.

I currently have the following:

SELECT * FROM MyTable
WHERE (dbo.MyTable.BatchID =(SELECT MAX(BatchID) FROM dbo.MyTable AS dbo.MyTable1))

The BatchID's used in this table are of the format: yyyymmdd_hhmmss. Thus the first data upload for this AM was: 20190814_050127

The schedule for the uploads has changed from once an hour to 3 times an hour.

When the report which uses this data was run during the 5:00 AM hour the MAX(BatchID) worked just fine.

But now the report which uses this data can be run as late a during the 7:00 AM hour - Thus, I need to target the MIN(BatchID) for the 5:00 AM hour.

What I have tried are variations of: MIN(FORMAT; MIN(CAST; MIN(CONVERT - Etc.

In plain English, what I'm looking for is something that will return records for current day that equals the earliest run time during the 5:00 AM hour.

Below is where I gave up and came here with my tin cup in hand

SELECT * FROM MyTable
WHERE (dbo.MyTable.BatchID =(SELECT MIN(FORMAT(GETDATE('yyyymmdd') FROM dbo.MyTable AS dbo.MyTable1))

Thank You so much for any help...

[ArviLaanemets]

Something like

SELCT TOP 1 mt.* FROM MyTable mt WHERE mt.BatchID >= FORMAT(Date, "yyyymmdd") ORDER BY mt.BatchID ASC

OR

 SELCT TOP 1 mt.* FROM MyTable mt WHERE mt.BatchID >= FORMAT(Date, "yyyymmdd") & "_05000" ORDER BY mt.BatchID ASC

[accesstos]

Give a try to this query for the fisrt batch of each day:

SELECT CLng(Left([BatchID],8)) AS TheDay, 
Min(CLng(Right([BatchID],6))) AS MinTime, 
Min([BatchID]) AS FirstBatch
FROM MyTable
GROUP BY CLng(Left([BatchID],8));

[RunTime91]

Arvil & Accesstos - Thank You ~


The code below did the trick

SELECT * FROM MyTable
WHERE MyTable.BatchID = (SELECT MIN(BatchID) FROM MyTable WHERE BatchID LIKE FORMAT(GetDate(),'yyyyMMdd') +'_05%))