Today Production - SQL Date Calculation case when

Question

I have an issue regarding date calculations.

I have a datetime column called CreatedLocalTime date with this format: 2015-11-15 19:48:50.000

I need to retrieve a new column called Prod_Date with:

if “CreatedLocalTime” between 
    (CreatedLocalTime 7 AM) 
    & (CreatedLocalTime+1 7 AM)
 return CreatedLocalTime date with DD/MM/YYYY format

On other words, today production = sum of yesterday from 7 AM till today at 7 AM.

Any help using case?

Answer 1

For day 7AM to day+1 7AM, you can try:

SELECT CAST(CreatedLocalTime as date) 
...
FROM ...
WHERE ...
    CreatedLocalTime >= DATEADD(hour, 7, CAST(CAST(CreatedLocalTime as date) as datetime)) 
AND 
    CreatedLocalTime  < DATEADD(hour, 31, CAST(CAST(CreatedLocalTime as date) as datetime)) 
...

For previous day 7AM to day 7AM, replace 7 by -14 and 31 by 7.