asked on

T-SQL query returns data from wrong year.

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.

ASKER CERTIFIED SOLUTION

debi_mela

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

debi_mela

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]

debi_mela

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]

DSpeckleback

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...