By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Select Between 17:00 Yesterday and 17:00 Today

code green
Expert 100+
P: 1,726
I can get the whole of yesterday and today using this query
Expand|Select|Wrap|Line Numbers
  1. SELECT site, ord_date,
  2. LEFT(RTRIM(LTRIM(descr)),24) account
  3. FROM orders
  4. WHERE ord_date BETWEEN 
  5. CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME)
  6.  AND 
  7. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 31 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Here, read some of this...

Happy Coding!!!

~ CK
Dec 31 '09 #2

code green
Expert 100+
P: 1,726
Here, read some of this
That doesn't teach me anything.
String literals are used for the dates throughout.
Or did I miss something?
Jan 1 '10 #3

P: 1
Please try this.

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

-bala
Jan 4 '10 #4

code green
Expert 100+
P: 1,726
Expand|Select|Wrap|Line Numbers
  1. DATEADD( HH, 17, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
  2.  
Good Thinking! Add 17 hours to midnight. Wow. I'll try that. Thanks.
Jan 4 '10 #5

code green
Expert 100+
P: 1,726
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
Expand|Select|Wrap|Line Numbers
  1. DATEADD(HH,17,CAST(CONVERT(VARCHAR,DATEADD(
  2.      DAY,-1,GETDATE()) ,23) AS DATETIME)) yesterday1700 
Jan 8 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.