Solved

T-SQL query returns data from wrong year.

Posted on 2004-03-29
4
266 Views
Last Modified: 2012-08-14
I have a query that prompts for a begin and end date and then pulls data from between those two dates.  My problem is that if I type 03/25/2004 or 3/25/04 for a begin and end date I end up with transaction that happened on 3/25/03.  
CODE:
ALTER PROCEDURE dbo.[Deposit Transactions by form of pmt]
(@BEGIN_DATE DATETIME,
@END_DATE DATETIME)
AS SELECT     dbo.[Payment Info].[Form of Pmt], dbo.[Payment Info].[Pmt Date], dbo.[Payment Info].Amount, dbo.[Payment Info].[Account Number],
                      dbo.[Hockey Season Tickets].[First Name] + ' ' + dbo.[Hockey Season Tickets].[Last Name] AS Name, dbo.[Hockey Season Tickets].[Company Name],
                      dbo.[Payment Info].[Check/Credit #], dbo.[Payment Info].[Pmt for]
FROM         dbo.[Hockey Season Tickets] INNER JOIN
                      dbo.[Payment Info] ON dbo.[Hockey Season Tickets].[Account Number] = dbo.[Payment Info].[Account Number]
WHERE     (CONVERT(nvarchar(8), dbo.[Payment Info].[Pmt Date], 101) >= CONVERT(nvarchar(8), @BEGIN_DATE, 101)) AND (CONVERT(nvarchar(8),
                      dbo.[Payment Info].[Pmt Date], 101) <= CONVERT(nvarchar(8), @END_DATE, 101)) AND (dbo.[Payment Info].[Pmt for] = N'deposit')
ORDER BY dbo.[Payment Info].[Pmt Date], dbo.[Hockey Season Tickets].[Company Name]

I know I missing something simple, but I'm going crazy looking for it.
0
Comment
Question by:BCooper
[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
  • 3
4 Comments
 
LVL 3

Accepted Solution

by:
debi_mela earned 125 total points
ID: 10705967
make nvarchar(10) instead of 8..


ALTER PROCEDURE dbo.[Deposit Transactions by form of pmt]
(@BEGIN_DATE DATETIME,
@END_DATE DATETIME)
AS SELECT     dbo.[Payment Info].[Form of Pmt], dbo.[Payment Info].[Pmt Date], dbo.[Payment Info].Amount, dbo.[Payment Info].[Account Number],
                      dbo.[Hockey Season Tickets].[First Name] + ' ' + dbo.[Hockey Season Tickets].[Last Name] AS Name, dbo.[Hockey Season Tickets].[Company Name],
                      dbo.[Payment Info].[Check/Credit #], dbo.[Payment Info].[Pmt for]
FROM         dbo.[Hockey Season Tickets] INNER JOIN
                      dbo.[Payment Info] ON dbo.[Hockey Season Tickets].[Account Number] = dbo.[Payment Info].[Account Number]
WHERE     (CONVERT(nvarchar(10), dbo.[Payment Info].[Pmt Date], 101) >= CONVERT(nvarchar(10), @BEGIN_DATE, 101)) AND (CONVERT(nvarchar(10),
                      dbo.[Payment Info].[Pmt Date], 101) <= CONVERT(nvarchar(10), @END_DATE, 101)) AND (dbo.[Payment Info].[Pmt for] = N'deposit')
ORDER BY dbo.[Payment Info].[Pmt Date], dbo.[Hockey Season Tickets].[Company Name]

0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10705994
Also if both the datatypes are datetime, compare them without converting to char...

ALTER PROCEDURE dbo.[Deposit Transactions by form of pmt]
(@BEGIN_DATE DATETIME,
@END_DATE DATETIME)
AS SELECT     dbo.[Payment Info].[Form of Pmt], dbo.[Payment Info].[Pmt Date], dbo.[Payment Info].Amount, dbo.[Payment Info].[Account Number],
                      dbo.[Hockey Season Tickets].[First Name] + ' ' + dbo.[Hockey Season Tickets].[Last Name] AS Name, dbo.[Hockey Season Tickets].[Company Name],
                      dbo.[Payment Info].[Check/Credit #], dbo.[Payment Info].[Pmt for]
FROM         dbo.[Hockey Season Tickets] INNER JOIN
                      dbo.[Payment Info] ON dbo.[Hockey Season Tickets].[Account Number] = dbo.[Payment Info].[Account Number]
WHERE   dbo.[Payment Info].[Pmt Date] >= @BEGIN_DATE AND
             dbo.[Payment Info].[Pmt Date] <=  @END_DATE, 101
 AND (dbo.[Payment Info].[Pmt for] = N'deposit')
ORDER BY dbo.[Payment Info].[Pmt Date], dbo.[Hockey Season Tickets].[Company Name]

0
 
LVL 3

Expert Comment

by:debi_mela
ID: 10706005
sorry here is the correct version...


ALTER PROCEDURE dbo.[Deposit Transactions by form of pmt]
(@BEGIN_DATE DATETIME,
@END_DATE DATETIME)
AS SELECT     dbo.[Payment Info].[Form of Pmt], dbo.[Payment Info].[Pmt Date], dbo.[Payment Info].Amount, dbo.[Payment Info].[Account Number],
                      dbo.[Hockey Season Tickets].[First Name] + ' ' + dbo.[Hockey Season Tickets].[Last Name] AS Name, dbo.[Hockey Season Tickets].[Company Name],
                      dbo.[Payment Info].[Check/Credit #], dbo.[Payment Info].[Pmt for]
FROM         dbo.[Hockey Season Tickets] INNER JOIN
                      dbo.[Payment Info] ON dbo.[Hockey Season Tickets].[Account Number] = dbo.[Payment Info].[Account Number]
WHERE   dbo.[Payment Info].[Pmt Date] >= @BEGIN_DATE AND
             dbo.[Payment Info].[Pmt Date] <=  @END_DATE
 AND (dbo.[Payment Info].[Pmt for] = N'deposit')
ORDER BY dbo.[Payment Info].[Pmt Date], dbo.[Hockey Season Tickets].[Company Name]

0
 

Expert Comment

by:DSpeckleback
ID: 10706083
Consider using:
WHERE   dbo.[Payment Info].[Pmt Date] BETWEEN @BEGIN_DATE AND @END_DATE.

Also keep in mind that if the payment date includes the time and you are looking for all payments on 03/25/2004, the @END_DATE should include 23:59:59 (or be < 03//26/2004).

Just some thoughts...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 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