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