October 20, 2016 at 10:06 am
Why do my dates show differently as they are the SAME date/time? 1 shows the hour as 9 the other as 10. I want them both to show as 9.
I set a date as: '2016-10-19 09:59:59.997' and display it.
DECLARE @PortsLastEndDate datetime
SET @PortsLastEndDate = '2016-10-19 09:59:59.997'
SELECT 'PortsLastEndDate: ' + Cast(@PortsLastEndDate as varchar)
@Last Date: Oct 19 2016 9:59AM
Then I retrieve the last date from a table which has a date value of: 2016-10-19 09:59:59.997 and the EndDate is defined in the table as: [EndDate] [datetime] NOT NULL.
SELECT TOP 1 @PortsLastEndDate = EndDate
FROM dbo.BandwidthLogCalculatedTest6
WHERE PortIndex = 8
ORDER BY EndDate DESC
SELECT @PortsLastEndDate
@PortsLastEndDate: 2016-10-19 10:00:00.997
------- The following is how I create the table and how I insert rows into the table that I query above in my example.
-- Table create script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BandwidthLogCalculatedTest6](
[BandwidthLogCalculatedId] [int] IDENTITY(1,1) NOT NULL,
[SwitchID] [int] NOT NULL,
[PortIndex] [int] NOT NULL,
[BandwidthIn] [bigint] NOT NULL,
[BandwidthOut] [bigint] NOT NULL,
[BandwidthInMbps] [decimal](7, 2) NOT NULL,
[BandwidthOutMbps] [decimal](7, 2) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[EntryType] [varchar](25) NOT NULL,
CONSTRAINT [BandwidthLogCalculatedIdTest6_PK] PRIMARY KEY CLUSTERED
(
[BandwidthLogCalculatedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Code that inserts into the table I am selecting from:
DECLARE @JustCurrentDate date,
@StartDateTime datetime
DECLARE @StartRangeTime time(3) -- just the 1st 3 of the milliseconds.
DECLARE @EndRangeTime time(3) -- just the 1st 3 of the milliseconds.
SELECT TOP 1 @StartDateTime = TimeStamp
FROM dbo.BandwidthLogTest6 a
INNER JOIN dbo.Bandwidth b ON ( b.SwitchIp = @SwitchIP AND a.PortIndex = b.SwitchPort )
ORDER BY a.TimeStamp DESC
-- Set the date. Time is NOT included.
SET @JustCurrentDate = CONVERT (date, @StartDateTime)
SET @StartRangeTime = '09:00:00.000'
SET @EndRangeTime = '09:59:59.997'
SET @StartRangeDateTime = CAST(@JustCurrentDate AS DATETIME) + CAST(@StartRangeTime AS DATETIME)
SET @EndRangeDateTime = CAST(@JustCurrentDate AS DATETIME) + CAST(@EndRangeTime AS DATETIME)
INSERT INTO #BandwidthLogCalculatedEach24Summed (
SwitchID,
PortIndex,
BandwidthIn,
BandwidthOut,
BandwidthInMbps,
BandwidthOutMbps,
StartDate,
EndDate,
EntryType,
HourNumber )
SELECT SwitchID,
PortIndex,
SUM(BandwidthIn),
SUM(BandwidthOut),
AVG(BandwidthInMbps),
AVG(BandwidthOutMbps),
@StartRangeDateTime,
@EndRangeDateTime,
EntryType, -- has 'Second'.
9
FROM #BandwidthLogCalculatedAll24Hours
WHERE ( StartDate >= @StartRangeDateTime AND StartDate <= @EndRangeDateTime )
GROUP BY SwitchID,
PortIndex,
EntryType
-- The table I am doing the select from above.
INSERT INTO dbo.BandwidthLogCalculatedTest6 (
-- Has an identity key.
SwitchID,
PortIndex,
BandwidthIn,
BandwidthOut,
BandwidthInMbps,
BandwidthOutMbps,
StartDate,
EndDate,
EntryType )
SELECT SwitchID,
PortIndex,
BandwidthIn,
BandwidthOut,
BandwidthInMbps,
BandwidthOutMbps,
StartDate,
EndDate,
'Hour' -- set to a 'Hour' row.
FROM #BandwidthLogCalculatedEach24Summed
ORDER BY SwitchID,
PortIndex,
HourNumber
October 20, 2016 at 2:16 pm
Does this query return the the same value in both columns?
And is it '2016-10-19 09:59:59.997' or '2016-10-19 10:00:00.997'?
declare @StartdateTime datetime;
SELECT TOP 1 @PortsLastEndDate = EndDate
FROM dbo.BandwidthLogCalculatedTest6
WHERE PortIndex = 8
ORDER BY EndDate DESC
SELECT TOP 1 @PortsLastEndDate as PortsLastEndDate, EndDate
FROM dbo.BandwidthLogCalculatedTest6
WHERE PortIndex = 8
ORDER BY EndDate DESC
Are you asking because you seem to be getting the incorrect last value according to your where clause?
You will get more consistent results if you replace this
WHERE ( StartDate >= '2016-10-19 09:00:00.000'
AND StartDate <= '2016-10-19 09:59:59.997' )
with this
WHERE ( StartDate >= '2016-10-19 09:00:00.000'
AND StartDate < '2016-10-19 10:00:00.000' )
October 21, 2016 at 8:33 am
We ran a Windows update and did a reboot and it fixed the problem. Thanks to all though for your input.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply