Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Course Of The Month
8 days, 19 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?
Active 5 days ago
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.
Active 5 days ago
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 2 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
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 19 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.