Solved

Sql Datetime in where clause?

Posted on 2012-12-21
6
271 Views
Last Modified: 2012-12-26
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(qty_invoiced)
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(qty_invoiced)
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.
0
Comment
Question by:spoye
[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
 
LVL 15

Expert Comment

by:tim_cs
ID: 38713313
We need some examples of "proper results." What does your data look like, what are the expected results and what are the actual results?
0
 

Author Comment

by:spoye
ID: 38713343
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.
0
 

Author Comment

by:spoye
ID: 38713374
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.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 22

Expert Comment

by:Steve Wales
ID: 38713377
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:

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)

Open in new window

0
 

Accepted Solution

by:
spoye earned 0 total points
ID: 38713386
The problem was the group by.  It is now running properly I am very sorry.  Thank you for the help.  The first query had the group by on record date.
0
 

Author Closing Comment

by:spoye
ID: 38720913
The group by incorrect.
0

Featured Post

Simplifying Server Workload Migrations

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.

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.

Join & Ask a Question