Hi,
I am looking to calculate some data. We have orders dropping every day, and I need to calculate the number of orders that dropped between 6pm yesterday till 6pm today. I tried doing
DATEADD(HOUR,-6,cast(CONVERT(VARCHAR,CREATE_DATE)+ ' '+ CONVERT(VARCHAR,CREATE_TIME) as datetime))
but that just changes every hour to go back 6 hours. I need to create a custom datetime column or some calculation that will grab orders created after 6pm the previous day, till 5:59pm the next day. Any ideas on how to do this would be great.
Don't use a function against the column, that can make performance worse, potentially much worse.
WHERE CREATE_DATE = (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND CREATE_TIME >= '18:00:00') OR
(CREATE_DATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND CREATE_TIME < '18:00:00')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 4, 2021 at 5:12 pm
Thanks for that. How would I create this as a column, rather than it being in a filter? I have a view to which I need to add this to, along with many other calculations, so it would be easier if I added this as a column rather than in the where clause.
October 4, 2021 at 5:39 pm
Maybe something like this?:
ALTER TABLE dbo.your_table_name ADD REPORT_DATE AS CAST(DATEADD(DAY, CASE WHEN CREATE_TIME >= '18:00:00' THEN 1 ELSE 0 END, CREATE_DATE) AS date) PERSISTED;
...
WHERE REPORT_DATE = CAST(GETDATE() AS date)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply