Solved

Sql Datetime in where clause?

Posted on 2012-12-21
6
258 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

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.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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