CODEWITHSUNDEEP
sqlsql-serversql-date-functions
Lyndsey Cupit
Lyndsey Cupit

Reputation: 3

SQL Code to Pull data from previous day 4am up to 3:59am current day

I am trying to pull all data for the previous day starting from 4am until 3:59am CST of the current day.

For example: Today is 11/4/20 so I would want all data from 11/3/2020 04:00 - 11/4/2020 03:59

Can somebody help me with this? I feel like I am over thinking this and can't figure it out!

SELECT 
      [auth_account_number] as [Employeee ID]
,'Pearl Card Withdraw $' as [Time Off]
      ,tm.updated_date_time as [Date]
      ,[payment_amount] as [Total Time]
  FROM [ig_business].[dbo].[Check_GA_Account_Charge_Detail] cd
  inner join [ig_transaction].[dbo].[Transaction_Master] tm on cd.transaction_data_id=tm.transaction_data_id 

  where tender_dim_id='104'
  and tm.updated_date_time >= convert(datetime, dateadd(day, 1, convert(date, getdate()))) + '04:00:00' 
   and tm.updated_date_time < convert(datetime, convert(date, getdate())) + '04:00:00'

Upvotes: 0

Views: 248

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Assuming you have a date/time column, you can use:

select t.*
from t
where dt >= convert(datetime, dateadd(day, -1, convert(date, getdate()))) + '04:00:00' and
      dt < convert(datetime, convert(date, getdate())) + '04:00:00'

Upvotes: 1

Lyndsey Cupit
Lyndsey Cupit

Reputation: 3

I was able to figure it out I used this code

  and tm.updated_date_time <= dateadd(day, datediff(day, 0, getdate()), 0) + '4:00'
  and tm.updated_date_time > dateadd(day, datediff(day, 0, getdate()-1), 0) + '4:00'

Upvotes: 0

Related Questions