Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Course Of The Month
7 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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 today
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
This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| average of calculation (TSQL) | 4 | 45 | |
| SQL server client app | 3 | 74 | |
| Estimating my database size | 7 | 51 | |
| ms sql + help with query | 2 | 43 |
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article.
SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Suggested Courses
Course of the Month7 days, 5 hours left to enroll
732 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.