Select statement based on getdate() depending on time of day

Question

Post reply

Select statement based on getdate() depending on time of day

delacruz.ald

SSC Veteran

Points: 267
More actions
April 24, 2018 at 3:16 pm

#328480

I have a need for a select statement which uses current date/time and if hour is less than 15(3PM) the starting date is set for one day less. Here is my code, but as I understand it, it's T-SQL and it doesn't play nice when i'm trying to build my view. It works but I get some errors/messages that the view can't be displayed. I also can't save the view.
****************************
DECLARE @from datetime;
set @from = GETDATE();
if (DATEPART(HOUR, @from) < 15) set @from = dateadd(day, -1, @from)
DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) + '15:00:00');
DECLARE @end_date datetime = DATEADD(HOUR, 12, @start_date);
SELECT * from line4newdate where [NewDate] between @start_date and @end_date
*******************************
Any help in making this thing play nice in Management Studio is greatly appreciated, even if it's a complete rewrite.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

delacruz.ald SSC Veteran Points: 267 More actions · Answer 1

After further researching....

Local variables are not allowed in a VIEW.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

delacruz.ald - Tuesday, April 24, 2018 3:16 PM
I have a need for a select statement which uses current date/time and if hour is less than 15(3PM) the starting date is set for one day less. Here is my code, but as I understand it, it's T-SQL and it doesn't play nice when i'm trying to build my view. It works but I get some errors/messages that the view can't be displayed. I also can't save the view.
****************************
DECLARE @from datetime;
set @from = GETDATE();
if (DATEPART(HOUR, @from) < 15) set @from = dateadd(day, -1, @from)
DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) + '15:00:00');
DECLARE @end_date datetime = DATEADD(HOUR, 12, @start_date);
SELECT * from line4newdate where [NewDate] between @start_date and @end_date
*******************************
Any help in making this thing play nice in Management Studio is greatly appreciated, even if it's a complete rewrite.

Hope this gets you to where you are going.

DECLARE @TestDate DATETIME = DATEADD(HOUR,-2,GETDATE()), @StartDate DATETIME, @EndDate DATETIME;

SELECT @StartDate = DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, @TestDate)),0)), @EndDate = DATEADD(HOUR, 12, DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, @TestDate)),0)));

SELECT @StartDate, @EndDate;

GO

DECLARE @TestDate DATETIME = GETDATE(), @StartDate DATETIME, @EndDate DATETIME;

SELECT @StartDate = DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, @TestDate)),0)), @EndDate = DATEADD(HOUR, 12, DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, @TestDate)),0)))

SELECT @StartDate, @EndDate;

GO

CREATE VIEW dbo.Aview AS

SELECT ...

FROM ...

WHERE

[SomeDate] BETWEEN DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, GETDATE())),0))

AND DATEADD(HOUR, 12, DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, GETDATE())),0)));

delacruz.ald SSC Veteran Points: 267 More actions · Answer 3

For anyone that may need something like this in the future. Here's the code I came up with.

SELECT ActualW, LineDate, NewDate, Description, MinusTolerance, PlusTolerance, TargetW, LineTime

FROM dbo.line4Newdate

WHERE (NewDate >= CASE WHEN datepart(hh, getdate()) < 15 THEN dateadd(hh, - 9, dateadd(dd, datediff(dd, 0, getdate()), 0)) ELSE dateadd(hh, 15, dateadd(dd, datediff(dd, 0, getdate()), 0)) END) AND (NewDate < CASE WHEN datepart(hh,

getdate()) < 15 THEN dateadd(hh, 3, dateadd(dd, datediff(dd, 0, getdate()), 0)) ELSE dateadd(hh, 27, dateadd(dd, datediff(dd, 0, getdate()), 0)) END)

It's not fully tested, I'll have to change the date to midnight to make sure it works.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

delacruz.ald - Wednesday, April 25, 2018 1:56 PM
For anyone that may need something like this in the future. Here's the code I came up with.SELECT ActualW, LineDate, NewDate, Description, MinusTolerance, PlusTolerance, TargetW, LineTimeFROM dbo.line4NewdateWHERE (NewDate >= CASE WHEN datepart(hh, getdate()) < 15 THEN dateadd(hh, - 9, dateadd(dd, datediff(dd, 0, getdate()), 0)) ELSE dateadd(hh, 15, dateadd(dd, datediff(dd, 0, getdate()), 0)) END) AND (NewDate < CASE WHEN datepart(hh, getdate()) < 15 THEN dateadd(hh, 3, dateadd(dd, datediff(dd, 0, getdate()), 0)) ELSE dateadd(hh, 27, dateadd(dd, datediff(dd, 0, getdate()), 0)) END)It's not fully tested, I'll have to change the date to midnight to make sure it works.

Looks to me like the code I posted does the same thing without using a CASE clause:
SELECT [ActualW] , [LineDate] , [NewDate] , [Description] , [MinusTolerance] , [PlusTolerance] , [TargetW] , [LineTime] FROM [dbo].[line4Newdate] WHERE ([NewDate] >= DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, GETDATE())),0))) AND ([NewDate] < DATEADD(HOUR, 12, DATEADD(HOUR, 15, DATEADD(DAY,DATEDIFF(DAY,0,DATEADD(HOUR, -15, GETDATE())),0))));

delacruz.ald SSC Veteran Points: 267 More actions · Answer 5

I had to use the CASE statement so that the 'report' would work after midnight.
For example,
if the report is ran before 3PM, the start time will be 3PM the previous day through (end time) 3AM the current day.
If the report is ran after 3PM, the start time is 3PM current day through 3AM the next day.

Thanks for your post though, it really help to point me in the direction i needed to go.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

delacruz.ald - Thursday, April 26, 2018 10:04 AM
I had to use the CASE statement so that the 'report' would work after midnight.
For example,
if the report is ran before 3PM, the start time will be 3PM the previous day through (end time) 3AM the current day.
If the report is ran after 3PM, the start time is 3PM current day through 3AM the next day.
Thanks for your post though, it really help to point me in the direction i needed to go.

The code I posted works the same. Test it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis - Thursday, April 26, 2018 10:23 AM
delacruz.ald - Thursday, April 26, 2018 10:04 AM
I had to use the CASE statement so that the 'report' would work after midnight.
For example,
if the report is ran before 3PM, the start time will be 3PM the previous day through (end time) 3AM the current day.
If the report is ran after 3PM, the start time is 3PM current day through 3AM the next day.
Thanks for your post though, it really help to point me in the direction i needed to go.
The code I posted works the same. Test it.

Here is the code pulled from the query and setup to allow testing with different values:
DECLARE @TestDate DATETIME;

SET @TestDate = '2018-04-25 01:00:00';