Need help with calculating data between 6pm-6pm instead of 12am-12am

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

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