Select Between 17:00 Yesterday and 17:00 Today

[code green]

I can get the whole of yesterday and today using this query

SELECT site, ord_date,
LEFT(RTRIM(LTRIM(descr)),24) account
FROM orders
WHERE ord_date BETWEEN 
CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME)
 AND 
CAST(GETDATE() AS DATETIME)

Casting GETDATE to INT truncates the time element and casting back to DATETIME resets the time element to midnight.
I figured if I did something like this it would give me 17:00 yesterday

CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT)+' 17:00:00.000' AS DATETIME)

But error:

Syntax error converting the varchar value ' 17:00:00.000' to a column of data type int.

I must be close.
Any help appreciated

[ck9663]

Here, read some of this...

Happy Coding!!!

~ CK

[code green]

Here, read some of this

That doesn't teach me anything.
String literals are used for the dates throughout.
Or did I miss something?

[reachenbee]

Please try this.

DATEADD( HH, 17, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))

-bala

[code green]

DATEADD( HH, 17, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
 

Good Thinking! Add 17 hours to midnight. Wow. I'll try that. Thanks.

[code green]

Just an update for anybody interested.
CAST ...TO INT is unreliable.
It can flip a day either way, probably something to do with rounding.
CONVERT to a date works better. then CAST back to DATETIME to get midnight.
And simply DATEADD any number of hours you like

DATEADD(HH,17,CAST(CONVERT(VARCHAR,DATEADD(
     DAY,-1,GETDATE()) ,23) AS DATETIME)) yesterday1700