Incorrect data brought back

Question

Post reply

Incorrect data brought back

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 6, 2007 at 5:40 am

#69841

Hi,
I have a stored proc that looks like this:
alter procedure usp__RptTNGAlerts
@day int
as
set nocount on
select ServerName, Alert, AlertDate from dbo.TNG_Alerts
where AlertDate > (select CAST(getdate()-@day AS varchar(50)))
order by ServerName

It brings back all the data before yesterday, but when I put an equal sign instead of the > , it brings back nothing.
What exactly am i doing wrong here. The output of the data looks like this.
ServerName Alert AlertDate
BEPARE01 TEst Failure Mar 1 2007 12:28PM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

pardon my misunderstanding what exactly are u supposed to be doing.

why do you have [where AlertDate > (select CAST(getdate()-@day AS varchar(50)))]

Everything you can imagine is real.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 2

the data must be greater than yesterday's date, sorry forgot to include, you have to enter a parameter.

usp__RptTNGAlerts '1'

The parameter entered would bring back data greater than yesterday's date, but when I specify "=" it brings back nothing

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

select ServerName, Alert, AlertDate from dbo.TNG_Alerts

where convert(varchar(10),AlertDate,101) > (select Convert(varchar(10),getdate()-@day,101))

order by ServerName

hope this should bring you the data expected.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Imke

It brings back nothing because you're matching on the time component of the datetime, as well as the date component.

Try

DATEDIFF(day, AlertDate, getdate()) > @day

instead of AlertDate > (select CAST(getdate()-@day AS varchar(50)))

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 5

thanks, but your query did'nt work...i got this from someone and it worked

select ServerName, Alert, alertdate from dbo.TNG_Alerts

where AlertDate > (select getdate()-@day)

order by ServerName,alertdate

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Hi Imke

You will still need to be careful with this: if the time component of an AlertDate which is the same date as the getdate() date, is before the time component of the getdate(), then it will be omitted from the result set. In other words, if you run this query at 23.59 tonight, hoping for yesterday's data, you will be in for a big surprise because you will only pick up data between 23.59.00.0 and 23.59.59.n - about one minutes' worth.

Here's a test with some sample data to show exactly what's happening. You will need to change the time component of the dates so that some of them are before, and some after, the time component of current getdate() on your server.

DECLARE @day int
SET @day = 1

CREATE TABLE #dates (AlertDate DATETIME)

INSERT INTO #dates (AlertDate)
SELECT '2007-07-05 13:45:00.0' UNION ALL
SELECT '2007-07-05 13:46:00.0' UNION ALL
SELECT '2007-07-05 13:47:00.0' UNION ALL
SELECT '2007-07-05 13:48:00.0' UNION ALL
SELECT '2007-07-05 13:49:00.0' UNION ALL
SELECT '2007-07-05 13:50:00.0' UNION ALL
SELECT '2007-07-05 13:51:00.0' UNION ALL
SELECT '2007-07-05 13:52:00.0'

SELECT getdate() -- RETURNS '2007-07-06 13:47:54.513'

SELECT * FROM #dates WHERE AlertDate >  (select getdate()-@day)
SELECT * FROM #dates WHERE DATEDIFF(day, AlertDate, getdate()) > @day - 1

DROP TABLE #dates

Now, when I ran this batch a few minutes ago, the first result set was 5 rows, the second was 8 rows.

Hope this helps

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

alter

procedure usp__RptTNGAlerts

(

@day int

)

as

set

nocount on

select

ServerName,

Alert,

AlertDate

from dbo.TNG_Alerts

where AlertDate >= DATEADD(DAY, DATEDIFF(DAY, @Day, CURRENT_TIMESTAMP), 0)

order by ServerName

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

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 8

Imke Cronje

SSCarpal Tunnel

Points: 4826

July 6, 2007 at 7:30 am

#717680

Thanks Peter, your script worked perfectly.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

FWIW, Chris had a solution that should work, except that the '>' should be '<'. You may want to try that, just to know that we're not ALL crazy here...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Heh heh good catch, Steve!

Also, Peter's treatment of dates is spot on, there's another current thread where this is "gently tested"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden