Date issue - when the time portion is 09:59:59.997

See more:

SQL

server

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 '@Last Date: ' + Cast(@PortsLastEndDate as varchar)

PortsLastEndDate: Oct 19 2016 9:59AM

Then I retrieve the EndDate from a table which has a datetime 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.

What I have tried:

-- 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 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