Strange problem with dates - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Strange problem with dates

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 09:16:57

Hi, I have a SP which I have been using without problems, untill now.
I tried it on another server, and suddenly I am getting strange results. Is there anyone who can tell me how this could happen.
the SP is:

ALTER PROCEDURE [dbo].[ZM_Articles_GetArchiveByMonth]
@PortalID int,
@moduleId int,
@Categories varchar(100),
@Month int,
@Year int,
@Age int,
@ShowAuthOnly bit,
@Featured bit,
@IgnoreShowInArchive bit
AS
DECLARE
@ThisMinute DateTime,
@ThisDay DateTime

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET @ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT
ArticleID,
PortalID,
ModuleId,
Author,
Title,
SubHead,
HeadlineDate,
Source,
Summary,
Keywords,
Article,
Authed,
[Email],
AuthorMailLink,
Featured,
NumberOfViews,
[ShowInArchive],
PublishDate,
[ExpireDate],
CreatedDate,
LastModifiedDate,
Categories = dbo.ConcatCategories(ArticleID)
FROM
ZMArticle
WHERE
PortalID = @PortalID
AND
(@Categories = '' OR ArticleID in (
SELECT DISTINCT ArticleID FROM dbo.ZMArticleCategories zac WHERE
(zac.CategoryID IN (SELECT intValue FROM dbo.csvToInt(@Categories)))
))
AND
(@ModuleID = -1 OR (ModuleID = @ModuleID))
AND
(@Age = 0 OR (HeadlineDate > DATEADD(DAY, @Age, @ThisDay)))
AND
MONTH(Headlinedate) = @Month
AND
YEAR(Headlinedate) = @Year
AND
(COALESCE(ExpireDate, DATEADD(DAY, 1, @ThisMinute)) < @ThisMinute)
AND
(@ShowAuthOnly = 0 OR Authed = 1)
AND
(@Featured = 0 OR Featured = 1)
AND
(@IgnoreShowInArchive = 1 OR ShowInArchive = 1)
ORDER BY
HeadlineDate
DeSC

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 09:20:39

Oh yeah, and here is the one I call before this one.
It also gives strange results:

ALTER PROCEDURE [dbo].[ZM_Articles_GetArchive]
@PortalID int,
@moduleId int,
@Categories varchar(100),
@Age int,
@ShowAuthOnly bit,
@Featured bit,
@IgnoreShowInArchive bit

AS

DECLARE
@ThisMinute DateTime,
@ThisDay DateTime

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET @ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
(@ModuleID = -1 OR (ModuleID = @ModuleID))
AND
/* Age is bv. -10, dus headlinedate moet groter zijn dan vandaag -10 dagen */
(@Age = 0 OR (HeadlineDate > DATEADD(DAY, @Age, @ThisDay)))
AND
(@ShowAuthOnly = 0 OR Authed = 1)
AND
/* COALESCE GEEFT DE EERSTE NIET NULL WAARDE */
(COALESCE(ExpireDate, DATEADD(DAY, 1, @ThisMinute)) < @ThisMinute)
AND
(@Featured = 0 OR Featured = 1)
AND
(@IgnoreShowInArchive = 1 OR ShowInArchive = 1)
GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 09:24:59

The second one (ZM_articles_GetArchive) for example gives me:

Number Count HeadlineDate
4 7 2008-03-01 00:00:00.000
3 10 2008-02-01 00:00:00.000
2 6 2008-01-01 00:00:00.000
1 1 2007-12-01 00:00:00.000

Which looks ok, except the numbers (7, 10, 6 and 1) are not right

This would mean I have 24 records for portal 0, while in fact, I only have 8.
It should return 4, 2, 1, 1

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 09:32:39

when I try the first SP (ZM_Articles_GetArchiveByMonth) which should return the archive by month, I also get strange results.
When I try to call 03-2008 for portal 0, I only get 1 record, while it should return 4 records.

And on other SQl installations, I have no problems with these SP's, they work just fine....

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 09:54:05

Could it be the (COALESCE (ExpireDate, DATEADD(DAY, 1, @ThisMinute)) < @ThisMinute)

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 10:32:04

Well 1 thing that helped was adding portalid = @portalid.
But still strange results though, now I only get back:

Number Count Headlinedate
1 1 2008-03-01 00:00:00.000

Then I tried changing to:

AS

DECLARE
@ThisMinute DateTime,
@ThisDay DateTime

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET @ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
PortalID=0
GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC

Which results the correct stuff, only it does not filter for a expiredate = null I think

The secret to creativity is knowing how to hide your sources. (Einstein)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 13:09:02

Are you passing NULL for any of you bit fields? That could throw things off.

Also, if you can provide some sample data and expected output, then we could pobably help you better. It's kind of hard to tell what's going on without any source data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources