Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Solved
Sql Datetime in where clause?
Posted on 2012-12-21
I am using a time field in my where clause that is returning the proper results..except for April and May below is the query?
declare @sdate date
declare @edate date
set @sdate = dateadd(YY,-1,GETDATE())
set @edate = GETDATE()
select @sdate, @edate
select datepart(m,recorddate),SUM
from inv_item
where item = 'f7000356'
and tax_date between @sdate and @edate
group by datepart(m,recorddate)
--------------------------
When i run the below the results are correct if i change the month to 05 again the results are correct?
select datepart(m,tax_date),SUM(q
from inv_item
where item = 'f7000356'
and tax_date between '04/01/2012' and '04/30/2012'
group by datepart(m,tax_date)
Please help.
declare @sdate date
declare @edate date
set @sdate = dateadd(YY,-1,GETDATE())
set @edate = GETDATE()
select @sdate, @edate
select datepart(m,recorddate),SUM
from inv_item
where item = 'f7000356'
and tax_date between @sdate and @edate
group by datepart(m,recorddate)
--------------------------
When i run the below the results are correct if i change the month to 05 again the results are correct?
select datepart(m,tax_date),SUM(q
from inv_item
where item = 'f7000356'
and tax_date between '04/01/2012' and '04/30/2012'
group by datepart(m,tax_date)
Please help.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
6 Comments
We need some examples of "proper results." What does your data look like, what are the expected results and what are the actual results?
Results from first query: (4 and 5 are incorrect)
1 411.00000000
2 107.00000000
3 360.00000000
4 668.00000000
5 164.00000000
6 836.00000000
7 422.00000000
8 30.00000000
10 81.00000000
11 432.00000000
12 180.00000000
Expected Results of 4 and 5 (There are 2 carring over from 4 to 5)
4 742.00000000
5 90.00000000
The two carring over both have the date 2012-04-30 00:00:00.000, they are both added to MAY resulting in the 164 total.
1 411.00000000
2 107.00000000
3 360.00000000
4 668.00000000
5 164.00000000
6 836.00000000
7 422.00000000
8 30.00000000
10 81.00000000
11 432.00000000
12 180.00000000
Expected Results of 4 and 5 (There are 2 carring over from 4 to 5)
4 742.00000000
5 90.00000000
The two carring over both have the date 2012-04-30 00:00:00.000, they are both added to MAY resulting in the 164 total.
It was created on 05/01/2012 tax_date was back dated to 04/30/2012, but i would think they results should still be returned properly from the first query also.
Active 4 days ago
Is it an option to change your query to include times, just to make sure you're picking up the right values ?
You shouldn't ever assume values if you're looking for exact results, specify exactly what you want:
You shouldn't ever assume values if you're looking for exact results, specify exactly what you want:
select datepart(m,tax_date),SUM(qty_invoiced)
from inv_item
where item = 'f7000356'
and tax_date between '04/01/2012 00:00:00.000' and '04/30/2012 23:59:59.999'
group by datepart(m,tax_date)
Featured Post
Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month8 days, 6 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.