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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

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

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