Home > Enterprise >  Get records for past month, day and a specific 24 hour period in the past
Get records for past month, day and a specific 24 hour period in the past

Time:03-04

Im very new to SQL Sever so this might be a silly question. At the moment I have:

SELECT timeslot_start_time
     ,name
     ,description
     ,etc
From Table1
WHERE Timeslot_start_time >= '2022-02-01' 
ORDER BY Timeslot_start_time

Q1. Instead of doing this manually, is there a way the table only gets data that is a month old from today automatically?

Q2. Additionally, my data is in this format: 22-02-15 21:45:00, with data reading every 15 minutes. Is there a way I can create a column(/table or whatever is best) that filters only the last 24hours automatically?

Q3. Additionally, is it possible to filter the last 24hrs but from a specific time. For example filtering the last 24hrs up to 10pm, So 10pm-10pm.

I hope all of that makes sense

CodePudding user response:

Your data could be queried using basic date arithmetic:

gets data that is a month old from today automatically?

"A month" is ambiguous (like past 30 days, all dates in previous month, etc). But most simple definition is to use dateadd and let SQL Server decide:

WHERE timeslot_start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP)

that filters only the last 24hours automatically?

WHERE timeslot_start_time >= DATEADD(DAY, -1, CURRENT_TIMESTAMP)

is it possible to filter the last 24hrs but from a specific time. For example filtering the last 24hrs up to 10pm, So 10pm-10pm.

DECLARE @d2 DATETIME = DATETIMEFROMPARTS(
    DATEPART(YEAR, CURRENT_TIMESTAMP),
    DATEPART(MONTH, CURRENT_TIMESTAMP),
    DATEPART(DAY, CURRENT_TIMESTAMP),
    22,
    0,
    0,
    0
);
IF @d2 > CURRENT_TIMESTAMP
    -- if 10pm today is in the future then use yesterday
    SET @d2 = DATEADD(DAY, -1, @d2);

SELECT ...
WHERE timeslot_start_time >= DATEADD(DAY, -1, @d2)
AND   timeslot_start_time <  @d2
  • Related