November 8, 2011 at 2:07 pm
The info on the forums have been very useful, first post though, since I have hit a wall with one particular query, and cannot find the specific answer in the forums
I am writing simple queries to grab info from previous days and or time intervals from a specific table.
The LogDate column is smalldatetime
Here is one query that works.
select * from table
where LogDate between dateadd(hour,10,DATEDIFF(d,0,getdate()-1)) and
dateadd(hour,11,DATEDIFF(d,0,getdate()-1))
order by LogDate desc
This returns all results for that time range for the previous day.
This query returns nothing work.
select * from table
where LogDate between dateadd(hour,10,DATEDIFF(d,0,getdate()-2)) and
dateadd(hour,11,DATEDIFF(d,0,getdate()-2))
order by LogDate desc
Now the individual date codes return results but only from "yesterday" so I am missing something.
select dateadd(hour,10,DATEDIFF(d,0,getdate()-1)) as Date1,
dateadd(hour,11,DATEDIFF(d,0,getdate()-1)) as Date2,
dateadd(hour,10,DATEDIFF(d,0,getdate()-2)) as Date3,
dateadd(hour,11,DATEDIFF(d,0,getdate()-2)) as Date4
returns
Date1 Date2 Date3 Date4
----------------------- ----------------------- ----------------------- -----------------------
2011-11-07 10:00:00.000 2011-11-07 11:00:00.000 2011-11-06 10:00:00.000 2011-11-06 11:00:00.000
Based on what I see here, I am simply changing the Day so what gives?
I know the answer will be simple.
Thanks
Be a well of knowledge . . . Not a fire hose. Those who appreciate your knowledge will come to you with a bucket and get what they need. "Drenching" others with your knowledge however, will cause them to look elsewhere.
November 8, 2011 at 2:17 pm
You have a pretty small window of time (1 hour). Are you certain that you have records between 10am and 11am from two days ago?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 2:25 pm
You are the man. Thanks Sean!!
For some reason when I was working with this a few weeks ago, I would have sworn that the interval I was checking had data in it. I got away from it for a while and fooled around with it today and you are right. That time frame has no data!!
Of course I should have checked that first but I was certain that it was something I was doing wrong.
Embarassing!!:pinch:
Be a well of knowledge . . . Not a fire hose. Those who appreciate your knowledge will come to you with a bucket and get what they need. "Drenching" others with your knowledge however, will cause them to look elsewhere.
November 8, 2011 at 2:49 pm
No worries. We have all done it more times than we care to admit. Glad that you figured it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply