Solved

Sql Datetime in where clause?

Posted on 2012-12-21
6
255 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore 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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now