different results that is wrong

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

different results that is wrong

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 06:33:27

Hi when I run this query....

SELECT     TOP 100 PERCENT COUNT(ReportID) AS Qty, DATENAME(mm, DateReported) AS MonthName, MONTH(DateReported) AS MonthNumber
FROM         dbo.tbl_Error_Reports AS o
WHERE     (DateReported BETWEEN CONVERT(DATETIME, '2007-01-01', 102) AND CONVERT(DATETIME, '2007-12-31', 102))
GROUP BY DATENAME(mm, DateReported), MONTH(DateReported)
ORDER BY MonthNumber

I get Qty=15 for November, but when I run this query I only get Qty=7

SELECT     COUNT(ReportID) AS Qty
FROM         dbo.tbl_Error_Reports
WHERE     (DateReported BETWEEN CONVERT(DATETIME, '2007-11-01', 102) AND CONVERT(DATETIME, '2007-11-30', 102))

I know that there should be 15 rows, but I can't figure out why there is different results. Does anyone know what could be wrong?

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 06:36:21

What's result you are getting?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 06:44:43

When I run the first query I get this result....

Qty Monthnamne MonthNumber
15 November 11
51 December 12

But when I run the second query I get

Qty = 7 (November Qty = 7)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 06:53:38

Most probable you are using time portion of DateReported and thus missing all records for November 30, 2007 (with the expection of records timed 00:00:00.000).

Use this instead for comaprison

SELECT	COUNT(ReportID) AS Qty
FROM	dbo.tbl_Error_Reports
WHERE	DateReported >= '20071101'
	AND DateReported < '20071201'

And this in your original query

SELECT		TOP 100 PERCENT
		COUNT(ReportID) AS Qty,
		DATENAME(MONTH, DateReported) AS MonthName,
		DATEPART(MONTH, DateReported) AS MonthNumber
FROM		dbo.tbl_Error_Reports
WHERE		DateReported >= '20070101'
		AND DateReported < '20080101'
GROUP BY	DATENAME(MONTH, DateReported),
		DATEPART(MONTH, DateReported)
ORDER BY	DATEPART(MONTH, DateReported)

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 07:23:10

Hi Peso

That worked fine in Sql server, now I just have to convert the datetime from 2007-11-01 00:00:00 to 2007-11-01 in order to get it to work. By the way should I pass the parameters as nVarChar or datetime?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 07:36:33

Always use proper datatypes. Use DATETIME.

Also, if DateReported column is DATETIME or SMALLDATETIME, there is no need for substituting anything.

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 07:45:38

Peso

When I now run this procedure...

@Date1 DateTime,
@Date2 DateTime
AS
BEGIN
SET NOCOUNT ON;
SELECT     COUNT(ReportID) AS Qty
FROM         dbo.tbl_Error_Reports
WHERE     (DateReported >= @Date1) AND (DateReported < @Date2)

and pass "2007-11-01" as Date1 and "2007-11-30" as date2, then I get 7 results. But if I change date2 to "2007-12-01" then I get 15 results?! Do you know what happends?

PS: DateReported is SmallDatetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 08:05:40

Yes, same thing as before. You do not take into account the TIME portion of the datatime value.

CREATE PROCEDURE dbo.uspTest
(
	@FromDate DATETIME,	-- use normal first day of month ie 20071101
	@ToDate DATETIME	-- use normal last day of month ie 20071130
)
AS

SET NOCOUNT ON

DECLARE	@Temp DATETIME

-- If @FromDate is later than @ToDate, switch the two dates
IF @FromDate > @ToDate
	SELECT	@Temp = @FromDate,
		@FromDate = @ToDate,
		@ToDate = @Temp

-- Remove the time portion and set @ToDate to one day later
SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
	@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @FromDate), '19000101')

-- Show the expected output
SELECT	COUNT(ReportID) AS Qty
FROM	dbo.tbl_Error_Reports
WHERE	DateReported >= @FromDate
	AND DateReported < @ToDate

With this approach, you can utilize any existing index over DateReported column.

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 08:18:12

Hi Peso

Hmm, when I changed to that, I don't get any results at all...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 08:36:16

My bad. There is a typo in the last line of the "Remove time portion".

CREATE PROCEDURE dbo.uspTest
(
	@FromDate SMALLDATETIME,	-- use normal first day of month ie 20071101
	@ToDate SMALLDATETIME		-- use normal last day of month ie 20071130
)
AS

SET NOCOUNT ON

DECLARE	@Temp SMALLDATETIME

-- If @FromDate is later than @ToDate, switch the two dates
IF @FromDate > @ToDate
	SELECT	@Temp = @FromDate,
		@FromDate = @ToDate,
		@ToDate = @Temp

-- Remove the time portion and set @ToDate to one day later
SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
	@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @ToDate), '19000101')

-- Show the expected output
SELECT	COUNT(ReportID) AS Qty
FROM	dbo.tbl_Error_Reports
WHERE	DateReported >= @FromDate
	AND DateReported < @ToDate

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-15 : 08:39:22

Ahhh, now I get the correct result. Thanks a million!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources