September 16, 2013 at 1:58 pm
Have this query that if you run it past the 16 hour it throughs this error....I"m stumped?? If you run it as is runs fine, but if you try to designate a specific hour bombs out. What am I missing??
ALTER PROCEDURE ****
(
@Hour INT = NULL ,
@Application VARCHAR(30) = 'Shamoo'
)
AS
DECLARE @UTCDiff INT ,
@Start DATETIME ,
@End DATETIME
IF @Hour IS NULL
SELECT @Hour = DATEPART(hh, GETDATE()) - 1
--SET @Hour = '11' -- (INT Value. Ex: "11" for 11 o'clock of current day hour)
--SET @Application = ''
-- CHOOSE FROM :
-- COCO
-- MMS
-- PAS
-- SHAMOO
-- TOOLS
-- UBERMAIL
SELECT @UTCDiff = DATEDIFF(HOUR, SYSUTCDATETIME (), GETDATE()) * -1
SELECT @Start = CONVERT(NVARCHAR(50), DATEPART(MONTH, SYSUTCDATETIME ()))
+ '/' + CONVERT(NVARCHAR(50), DATEPART(DAY, SYSUTCDATETIME ())) + '/'
+ CONVERT(NVARCHAR(50), DATEPART(YEAR, SYSUTCDATETIME ())) + ' '
+ CONVERT(NVARCHAR(50), @Hour + @UTCDiff) + ':00:00'
SELECT @End = CONVERT(NVARCHAR(50), DATEPART(MONTH, SYSUTCDATETIME ())) + '/'
+ CONVERT(NVARCHAR(50), DATEPART(DAY, SYSUTCDATETIME ())) + '/'
+ CONVERT(NVARCHAR(50), DATEPART(YEAR, SYSUTCDATETIME ())) + ' '
+ CONVERT(NVARCHAR(50), @Hour + @UTCDiff) + ':59:59'
-- SiteCatalyst Errors
IF @Application = ''
BEGIN
SELECT [MESSAGE] ,
url ,
Cnt = COUNT(*) ,
MinTimeUTC = MIN(TimeUtc) ,
MaxTimeUTC = MAX(TimeUtc)
FROM weblogging.dbo.elmah_error WITH ( NOLOCK )
WHERE APPLICATION = 'SHAMOO'
AND timeutc BETWEEN @Start AND @End
AND MESSAGE != 'Invalid viewstate.' -- Excluded in SiteCatalyst alerts
GROUP BY [message] ,
url
ORDER BY cnt DESC ,
MESSAGE
END
ELSE
BEGIN
SELECT [MESSAGE] ,
url ,
Cnt = COUNT(*) ,
MinTimeUTC = MIN(TimeUtc) ,
MaxTimeUTC = MAX(TimeUtc)
FROM weblogging.dbo.elmah_error WITH ( NOLOCK )
WHERE APPLICATION = @Application
AND timeutc BETWEEN @Start AND @End
AND MESSAGE != 'Invalid viewstate.' -- Excluded in SiteCatalyst alerts
GROUP BY [message] ,
url
ORDER BY cnt DESC ,
MESSAGE
END
September 16, 2013 at 2:27 pm
It appears to be adjusting for time zone.
I'm in Eastern Standard Time (during daylight saving), and the @Start and @End datetime being generated are offset by 4 hours.
Specifying 1 returns a date starting at 5am.
Specifying 19 returns a date starting at 11pm.
Anything greater than 19 returns an error, as it results in a date with an hour > 23.
September 16, 2013 at 8:38 pm
The field is UTC time, but if you put in a paramter for hour it bombs out like I said after 16 +..... Is there a better way to do this?
September 17, 2013 at 7:41 am
The problem with your approach is it doesn't account for the day rolling over to tomorrow when converting to UTC time (the hour goes past midnight).
I would approach the problem by determining your start and end dates in your local time first, and then convert them to UTC time at the end using DateAdd, which will adjust the day for you if necessary.
Something like this (but you'll want to check this code very carefully as I just threw it together):
DECLARE @Hour INT
DECLARE @UTCDiff INT
DECLARE @Date DateTime
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET @Hour = 1
IF @Hour IS NULL
SELECT @Hour = DATEPART(hh, GETDATE()) - 1
-- Determine the hour difference between our time and UTC
SET @UTCDiff = DateDiff(Hour, GetDate(), GetUTCDate())
-- Get the date with the time stripped off
SELECT @Date = DATEADD(DAY, DATEDIFF(Day, 0, GetDate()), 0)
-- Add the hour we want to the date without time
SELECT @Start = DateAdd(Hour, @Hour, @Date )
-- Add another hour for the end date, and then subtract one second
SELECT @End = DateAdd(Second, -1, DateAdd(Hour, 1, @Start ))
PRINT @Start
PRINT @End
-- Convert the start and end date to UTC time
PRINT DateAdd(Hour, @UTCDiff, @Start)
PRINT DateAdd(Hour, @UTCDiff, @End)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply