May 3, 2012 at 7:59 am
So I was working with a performance metrics table, trying to load it into the DW.
One of the tasks I do is to round performance recording times to the minute (we don't need greater precision because this is about when somebody clicked a button during the day.... we probably don't need more than to the 15 minute mark or half-hour really).
So obviously we had a slight problem, I have a date on one record of 9999-06-05 21:13:49.907
Of course calculating the number of minutes elapsed since time slot 0 only works until you get to 5983-01-24 02:07:00.000
Any ideas how to do this with large datetime values?
SELECT CAST(DATEADD(n,DATEDIFF(n,0,'9999-06-05 21:13:49.907'),0) AS TIME)
May 3, 2012 at 8:13 am
Something like this?
DECLARE @date DATETIME = '9999-06-05 21:13:49.907';
SELECT CAST(DATEADD(n, CASE WHEN @date > ending THEN DATEDIFF(n, ending, @date) ELSE 0 END,
DATEADD(n, CASE WHEN @date > ending THEN value ELSE DATEDIFF(n, beginning, @date) END, beginning)) AS TIME)
FROM (VALUES ('5983-01-24 02:07:00.000', '1900-01-01 00:00:00', 2147483647)) a(ending, beginning, value);
May 3, 2012 at 8:19 am
That's pretty crafty,
I submitted it as a bug to connect. I like the work-around, but I believe they should have used a bigint instead of an int for datediff calculations.
Granted this is kind of silly.... we're 7000 years from the bad data I have, but managmeent's decision was to load all of the data whether or not it was future dated, and then in the DW show the users a quick way to find the junk so they can clean up the cause.
May 3, 2012 at 8:57 am
Hmmm it's not working for me though when I try to make it work with my table.
Table
CREATE TABLE [dbo].[ScreenViews](
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](30) NOT NULL,
[ScreenName] [varchar](30) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[LoadTime] [bigint] NOT NULL,
[EnterpriseID] [varchar](30) NOT NULL,
[SiteID] [varchar](30) NOT NULL,
[VersionNumber] [varchar](30) NOT NULL,
[MachineName] [varchar](30) NOT NULL,
[LicenseNumber] [varchar](50) NULL,
[ClientID] [varchar](30) NULL,
[LiveDataBase] [varchar](10) NULL,
CONSTRAINT [PK_Epic_ScreenViews] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Sample Data
INSERT INTO [dbo].[ScreenViews]
([UserName]
,[ScreenName]
,[TimeStamp]
,[LoadTime]
,[EnterpriseID]
,[SiteID]
,[VersionNumber]
,[MachineName]
,[LicenseNumber]
,[ClientID]
,[LiveDataBase])
VALUES
('Baduser',
'BadScreen1',
'9999-06-05 21:13:49.907',
102,
'BadEnterprise',
'BadSite1',
'21398789.12',
'BadMachine',
'BadLicense',
'BadClient',
'BadDB'
),
(
'Gooduser',
'GoodScreen1',
'2012-05-03 10:00:01.000',
54,
'GoodEnterprise',
'GoodSite1',
'5.3',
'GoodMachine',
'GoodLicense',
'GoodClient',
'GoodDB'
)
Query
DECLARE @beginning DATETIME = '1900-01-01 00:00:00'
DECLARE @ending DATETIME = '5983-01-24 02:07:00.000'
DECLARE @value INT = 2147483647
SELECT
a.UniqueID,
a.UserName,
a.ScreenName,
Executed_Date = CAST(a.TimeStamp AS DATE),
Executed_Time = CAST(DATEADD(n, CASE WHEN a.timestamp > @ending THEN DATEDIFF(n, @ending, a.timestamp) ELSE 0 END,
DATEADD(n, CASE WHEN a.timestamp > @ending THEN @value ELSE DATEDIFF(n, @beginning, a.timestamp) END, @beginning)) AS TIME),
Loaded_Date = CAST(GETDATE() AS DATE),
Loaded_time = CAST(DATEADD(n,DATEDIFF(n,0,GETDATE()),0) AS TIME),
a.LoadTime,
a.EnterpriseID,
a.SiteID,
a.VersionNumber,
a.MachineName,
LicenseNumber = ISNULL(a.LicenseNumber,'Unknown'),
a.ClientID,
a.LiveDataBase
FROM
[dbo].[ScreenViews] a
I'm sure it's my not getting your case statement, but I keep getting integer overflow from this.
Edit: Bug fix on the query... I hope nobody else has to deal with data loaded into a system 7000 years in the future.
May 3, 2012 at 9:04 am
mtassin (5/3/2012)
Hmmm it's not working for me though when I try to make it work with my table.I'm sure it's my not getting your case statement, but I keep getting integer overflow from this.
Strange, when I execute the code you provided it all works.
--edit--
I should clarify, it all worked once I added the "a" alias to the table name in the SELECT statement. Below is what is returned: -
UniqueID UserName ScreenName Executed_Date Executed_Time Loaded_Date Loaded_time LoadTime EnterpriseID SiteID VersionNumber MachineName LicenseNumber ClientID LiveDataBase
----------- ------------------------------ ------------------------------ ------------- ---------------- ----------- ---------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- ------------------------------ ------------
1 Baduser BadScreen1 9999-06-05 21:13:00.0000000 2012-05-03 16:06:00.0000000 102 BadEnterprise BadSite1 21398789.12 BadMachine BadLicense BadClient BadDB
2 Gooduser GoodScreen1 2012-05-03 10:00:00.0000000 2012-05-03 16:06:00.0000000 54 GoodEnterprise GoodSite1 5.3 GoodMachine GoodLicense GoodClient GoodDB
(2 row(s) affected)
May 3, 2012 at 9:07 am
aaah jeez... that will teach me...
I had a bunch of little queries at the end of my little window of SSMS
stuff like
SELECT CAST(DATEADD(n,DATEDIFF(n,0,'9999-06-05 21:13:49.907'),0) AS TIME)
Next time I'll remember to click the errror to see where it is. I figured it was the big query, I forgot about this little gem I had stuck at the bottom to work on diagnosing the problem.
thanks for the help!
May 3, 2012 at 10:05 am
SELECT MinutesDiff = ([Days]*24*60) + DATEDIFF(minute,[Days],@date)
FROM (SELECT [Days] = CAST(DATEDIFF(dd,0,@date) AS BIGINT)) d
😉
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply