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 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 above in my example:
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 row.
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 an Hour row.
FROM #BandwidthLogCalculatedEach24Summed
ORDER BY SwitchID, PortIndex, HourNumber