Solved

Sql Datetime in where clause?

Posted on 2012-12-21
6
267 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach 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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

860 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