An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Sql Datetime in where clause?
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.
Asked:
-
4
1 Solution
LVL 15
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.
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)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Your question, your audience. Choose who sees your identity—and your question—with question security.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now