SQLTeam.com | Weblogs | Forums

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

tsql
sql2014

#1

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


#2

Stop messing with seconds and/or subseconds in datetime comparisons. That's just asking for problems.

Specifically, instead of doing >= and <=, do a >= and < for the next greater time period. If you're basing on hour, less than the next hour; if you're going by day, less than the next day, etc..

WHERE StartDate >= datetime_time_9AM AND StartDate < DATEADD(HOUR, 1, datetime_time_9AM) /* that is, less than 10AM */

If you want all of yesterday's data:

StartDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
StartDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)


#3

Scott..3 things...One - The logic that I did not show - which is the real problem - is that I am trying to compare hours. But 1 date shows the hour as 9 and the other date (selected from the table ) shows the hour as 10. So I'm just trying to understand why they don't produce an hour of 9 in both of the cases that I presented as they both have the datetime value of '2016-10-19 09:59:59.997'. Two - I just wanted to show the code that is inserting the rows into the table which I query to get the EndDate but it returns the 2016-10-19 10:00:00.997 when the EndDate stored in the table is: 2016-10-19 09:59:59.997. I show the code so that you see how I derive the EndDate and insert it into the table. Three - Now as for your suggestion about the date/time range check that I am doing, I am trying to grab all the rows that fall into that 9 am hours time range on a given day. Which the time range would be from '09:00:00.000' to '09:59:59.997'. Correct?. So are you suggesting that I set a variable named @datetime_time_9AM = '09:00:00.000' and then on the other side of the where clause, add to it an hour to effectively get the hour range of '09:00:00.000' up to and including '09:59:59.997'?


#4

SQL didn't adjust the time by itself, there had to be some other manipulation of the time by code or some other action, such as converting to a different data type or adding some seconds in.

I am trying to grab all the rows that fall into that 9 am hours time range on a given day.

Great, no problem.

Which the time range would be from '09:00:00.000' to '09:59:59.997'. Correct?.

No. The exact range will vary depending on the data type. Therefore, as I stated and coded before, what you want to check for is:
table_column >= date_9AM and table_column < date_10AM
Note that this comparison works no matter what specific data type -- smalldatetime, datetime, datetime2, whatever -- is in the table.


#5

Scott.. My table column - EndDate - is defined as datetime and contains a datetime value such as:
2016-10-20 09:23:00.147.

So for that date type, I would have to set the date_9Am variable to a value of 2016-10-20 09:00:00.000.

Then the clause -

EndDate >= date_9AM and EndDate < DATEADD(HOUR, 1, date_9Am)

and the DATEADD(HOUR, 1, date_9Am) would equate to 2016-10-2010:00:00.000 thereby giving me the rows from
2016-10-20 09:00:00.000 to 2016-10-20 09:59:59.997.


#6

Yes, but that code is more flexible than hard-coding "09:59:59.997" and avoids having to subtract 3ms or do other peculiar datetime adjustments.


#7

Scott..the real problem - 1 date shows the hour as 9 and the other date (selected from the table ) shows the hour as 10, well...We ran a Windows update and did a reboot and it fixed the problem. Magic...ugh! But thanks to you and all though for your input. Much appreciated!


#8

Remember that what you see from a select is not always what is in the table. Example: Date...
SQL does NOT save the date as it is presented in the output grid, which has the value shown in readable form.


#9

I suspect the reason for that problem is some type of odd computation involving subtracting 3ms from the date to get a comparison date. You are determined to stick with your BETWEEN approach, but believe me, you will eventually miss rows and have other problems as datetime2 and other date/time types become more common in SQL Server.


#10

Scott..FYI..I am going to use your EndDate >= date_9AM and EndDate < DATEADD(HOUR, 1, date_9Am) approach. If that's what you mean by between.