CODEWITHSUNDEEP
sqlsql-serverdatetime
nsivakr
nsivakr

Reputation: 1595

List all records that were inserted during specific time of each day in datetime field

Would like to write a query that will list only the records, that were inserted between 8.00 PM and 11:59 PM for that day.

Note: The uploaddate field contains both date and time. Thus, the record could be inserted days earlier or later. I'm interested in filtering only records that was inserted on that day between 8.00 PM and 11.59 PM.

The uploadtime is available in the field uploaddate. I'm able to do it for a single day and also able to do it over multiple days using R/Python code.

enter image description here

In the above example, only the highlighted record should be include and the rest should be excluded.

Query to list for a single day.

select * 
from dbo.table
where t = '20220204'
  and uploaddate between '2022-02-04 20:00:00' and '2022-02-04 23:59:00' 
  and uploaddate is not null
order by uploaddate desc

R code to list for multiple days

thisDate = Sys.Date() 
currentDate = as.Date('2021-01-01', format("%Y-%m-%d"))
allRows = NULL
while ( currentDate < thisDate) {
  if ( format(currentDate, "%u") < 6 ) {
    thisDateStr = as.numeric(format(currentDate,"%Y"))*10000+as.numeric(format(currentDate,"%m"))*100+as.numeric(format(currentDate,"%d"))
    uploadDateStart = paste0(format(currentDate, format = "%Y-%m-%d") , " 20:00:00", sep="")
    uploadDateEnd= paste0(format(currentDate, format = "%Y-%m-%d") , " 23:59:00", sep="")
    query = paste0("select * from dbo.table where t = '", thisDateStr,"' and uploaddate is not null and uploaddate between '", uploadDateStart, "' and '", uploadDateEnd , "' order by uploaddate desc ", sep="")
    rowsToAdd =sqlQuery(dbhandle_prod,daily_market_data_query)
    if ( nrow(daily_market_data_results) > 0 ) {
      allRows = rbind(allRows, rowsToAdd )
    }
  }
  currentDate = currentDate + 1
}

Wondering, if it is possible to do it in SQL Server without having to write R/Python code.

Upvotes: 0

Views: 961

Answers (1)

Charlieface
Charlieface

Reputation: 71578

It seems you just want to ensure that uploaddate is on the same day as t, and after 20:00:00. So you can just check that using date artithmetic

SELECT
  * 
FROM dbo.[table] t
WHERE t.uploaddate >= DATEADD(hour, 20, CAST(t as datetime))
  AND t.uploaddate < DATEADD(day, 1, CAST(t as datetime))
ORDER BY
  uploaddate DESC;

If t is already a datetime value you can remove the two casts.

Upvotes: 1

Related Questions