Behaviour of column type 'DateTime2' between SQL 2014 and SQL 2022. This was found when our application's calculation for datetime difference of columns being type DateTime2 went wrong. Tried to find from google if there would be a specific setting in server property for datetime2 type of columns, but no luck. Noticed - DateTime2 improvements It is stated the behaviour is expected. Datetime2 with SQL 2014 is of precision 7 with only first 3 digits updated whereas its now 7 with SQL 2022 updated for all 7 digits. And so calculations with the datetime2 fields are turning mess after upgrading. Now, there are lot of columns in many tables which were defined as datetime2. It would take time to fix correction to all queries, application codes. Will there be any simple fix to this?

I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher. declare @LastSelectedDate DATETIME = '2021-11-09 13:52:29.187' declare @LastSelectedDate_1 DATETIME2(7) = '2021-11-09 13:52:29.1866667' SELECT IIF(@LastSelectedDate_1 > CAST(@LastSelectedDate AS DATETIME2), 1, 0) SELECT IIF(@LastSelectedDate_1 > @LastSelectedDate, 1, 0) Is this a bug or I am missing something? I am using sql server 2016.

I fell upon an integer format for dates for which I also know the date, but I do not know how to get to that date in TSQL and I also do not know how to get to the integer if I have the date: 700444 -> 1918-10-02 731573 -> 2003-12-24 739479 -> 2025-08-16 Those 6-digit numbers would fit as a counter for each day from 0001-01-01 onwards, I checked that by getting the number of days for one century from that date that is almost year 2000 and adding that to 1900: select DATEADD(dd,731573/20,'19000101') Out: 2000-02-24 00:00:00.000 But I cannot run select DATEADD(dd,731573/20,'10000101'), which throws: > The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Microsoft Learn says that TSQL allows dates only from 1753-01-01 onwards, see [datetime (Transact-SQL) Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16#description) , thus: select DATEADD(dd,731573/20,'17530101') Out: 1853-02-24 00:00:00.000 I cannot add the 731573 to year 1, though. Then I found [What is the significance of 1/1/1753 in SQL Server?](https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server) : *--(as said in one of the answers and at [Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so - DBA SE](https://dba.stackexchange.com/q/338742/212659) , take varchar(length) instead of just varchar)--* SELECT CONVERT(VARCHAR, DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100) SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100) Out: Jan 1 0001 12:00AM So that this is proven, the number *is* the days from the first day of year 0001. Now I wonder whether I can get there without formatting the datetime column as datetime2. My dates are all just in the 20th and 21st century so that I do not need the datetime2. I get the data as datetime and try to avoid a type conversion. How can I cast this integer in the seven-houndred-thousands as the counter of the days from the year 1 on to a date and how can I get from the date back to that integer without converting the date to datetime2?

We are working on a multi-database application (read and write to multiple databases). The datamodel is identical on the databases. We are inserting a timestamp (12 fraction digits) value in a datetime2(6) column in MSSQL however MSSQL rounds the value making it different to other databases where the extra fraction digits are ignored. Example: DECLARE @t TABLE(x DATETIME2(6)) INSERT @t SELECT '2017-03-28 14:00:59.4106489' SELECT x FROM @t Result: 2017-03-28 14:00:59.41064**9** Expected: 2017-03-28 14:00:59.41064**8** DB2 provides the expected result by throwing away the 7th fraction digit. How can we make MSSQL not round the datetime2 value ? EDIT The application writes a java.sql.Timestamp object with 12 fraction digits to DB2 and MSSQL. In DB2 the column is a TIMESTAMP(6) and in MSSQL a DATETIME2(6). DB2 truncates from 12 fraction digits down to 6. MSSQL rounds down to 6.

I have a SQL-Server database with large table that partition by a datetime2(2) column. Some (old) filegroups marked as READ_ONLY. Periodically I make the backup with READ_WRITE_FILEGROUPS option. I can successfully recover data from a READ WRITE partition. However, I cannot read the recovered data, I get the following error: > One of the partitions of index 'pk_myorderid' for table 'dbo.myorders'(partition ID 72057594043105280) resides on a filegroup ("YEAR2021") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result. If I change the data type to DATETIME or datetime2(7), no error occurs (of course if I request data from an restored range) Apart from the this issue, everything else is working properly. I created a test script to illustrate the problem. This script creates a test database, populates the table, backs up and restores the database. If, in this script, change datetime2(7) with datetime2(2) , the data becomes inaccessible after recovery. Test script:

USE MASTER

-- Reset environment
IF DB_ID('PartialDatabase') IS NOT NULL
BEGIN
	EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'PartialDatabase'
    ALTER DATABASE PartialDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
	DROP DATABASE PartialDatabase
END
GO
IF DB_ID('PartialDatabase_Recovery') IS NOT NULL
BEGIN
	EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'PartialDatabase_Recovery'
    ALTER DATABASE PartialDatabase_Recovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
	DROP DATABASE PartialDatabase_Recovery
END
GO


-- Create database
CREATE DATABASE [PartialDatabase] ON PRIMARY (
    NAME = N'PartialDatabase'
  , FILENAME = N'C:\SQLData\PartialDatabase_primary.mdf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )
  
  , FILEGROUP [YEAR2021]
(   NAME = N'PartialDatabase_YEAR2021'
  , FILENAME = N'C:\SQLData\PartialDatabase_YEAR2021.ndf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )

  , FILEGROUP [YEAR2022]
(   NAME = N'PartialDatabase_YEAR2022'
  , FILENAME = N'C:\SQLData\PartialDatabase_YEAR2022.ndf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )

  , FILEGROUP [YEAR2023]
(   NAME = N'PartialDatabase_YEAR2023'
  , FILENAME = N'C:\SQLData\PartialDatabase_YEAR2023.ndf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB ) 
 
  LOG ON
(   NAME = N'PartialDatabase_log'
  , FILENAME = N'C:\SQLData\PartialDatabase_log.ldf'
  , SIZE = 10240KB , FILEGROWTH = 10240KB )
GO

 
ALTER DATABASE [PartialDatabase] SET RECOVERY SIMPLE
GO
 
-- create partition FUNCTION & SCHEME
USE [PartialDatabase]
GO
CREATE PARTITION FUNCTION pf_myorders_date ([datetime2](7))	/*([datetime2](2))*/
	 AS RANGE RIGHT FOR VALUES
	('2022-01-01 00:00:00', 
	'2023-01-01 00:00:00')

CREATE PARTITION SCHEME ps_myorders_date AS PARTITION pf_myorders_date 
	TO ([YEAR2021], [YEAR2022],[YEAR2023])
GO
 
-- Create table
CREATE TABLE dbo.myorders
(
       myorder_id      INT                
     , myorder_date    [datetime2](7)						/*([datetime2](2))*/
     , myorder_details NVARCHAR(4000)	  
     , CONSTRAINT pk_myorderid PRIMARY KEY CLUSTERED (myorder_id, myorder_date)
)
ON ps_myorders_date(myorder_date)
GO

/*
Insert rows to all partitions
*/
INSERT INTO [PartialDatabase].dbo.myorders SELECT 1, '2020-01-01 10:00:00', 'year - 2020'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 2, '2021-01-01 10:00:00', 'year - 2021'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 3, '2022-01-01 10:00:00', 'year - 2022'
INSERT INTO [PartialDatabase].dbo.myorders SELECT 4, '2023-01-01 10:00:00', 'year - 2023'



GO


-- Mark old partitions as readonly
alter database [PartialDatabase] set SINGLE_USER with rollback immediate
GO

ALTER DATABASE [PartialDatabase] MODIFY FILEGROUP [YEAR2021] READONLY
ALTER DATABASE [PartialDatabase] MODIFY FILEGROUP [YEAR2022] READONLY

alter database [PartialDatabase] set MULTI_USER with rollback immediate
GO


-- Backup READ_WRITE filegroups
BACKUP DATABASE PartialDatabase 
READ_WRITE_FILEGROUPS
TO DISK = N'C:\SQLData\PartialDatabase_2023.bak'
WITH INIT, STATS = 10;
GO


--Restore READ_WRITE filegroups
RESTORE DATABASE [PartialDatabase_Recovery] 
READ_WRITE_FILEGROUPS
FROM DISK = N'C:\SQLData\PartialDatabase_2023.bak'
WITH PARTIAL, RECOVERY, 
MOVE 'PartialDatabase' TO 'C:\SQLData\PartialDatabase_Recovery_Primary.mdf',
MOVE 'PartialDatabase_YEAR2021' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2021.ndf',
MOVE 'PartialDatabase_YEAR2022' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2022.ndf',
MOVE 'PartialDatabase_YEAR2023' TO 'C:\SQLData\PartialDatabase_Recovery_YEAR2023.ndf',
MOVE 'PartialDatabase_log' TO 'C:\SQLData\PartialDatabase_Recovery_log.ldf'
GO

-- Request data located in the READ_WRITE filegroup 
SELECT [myorder_id]
      ,[myorder_date]
      ,[myorder_details]
  FROM [PartialDatabase_Recovery].[dbo].[myorders]
WHERE [myorder_date] >= '2023-01-01'

Of course, using the DATETIME type is a working solution, but what is the problem with datetime2(2)? I would prefer to solve the problem only by SQL database modifications, without affecting the software code base. Some applications are using constants in actual queries (not parameters). I am using SQL Server 2017 14.0.1000.169 (X64).

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'? Some things which **don't** work are listed below. --- SELECT @D + @T > Operand data type date is invalid for add operator. --- SELECT CAST(@D AS DATETIME2(7)) + @T > Operand data type datetime2 is invalid for add operator. --- SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D) > The datediff function resulted in an overflow. The number of dateparts > separating two date/time instances is too large. Try to use datediff > with a less precise datepart. \* The overflow can be avoided in Azure SQL Database and SQL Server 2016, using DATEDIFF_BIG . --- SELECT CAST(@D AS DATETIME) + @T > The data types datetime and time are incompatible in the add operator. --- SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME) > Returns a result but loses precision 2013-10-13 23:59:59.997

The code below generates this error: ###### ERROR > Msg 206, Level 16, State 2, Procedure usp_TRAC_PSE_Step_12_Resource_Request_OBJECT-Production-Part1, Line 93 Operand type clash: int is incompatible with datetime2 ###### CODE insert into [dbo].[pse__Resource_Request__c_LOAD] (OwnerId, pse__End_Date__c, pse__Milestone__c, pse__Percent_Allocated__c, pse__Planned_Bill_Rate__c, pse__Practice__c, pse__Project__c, pse__Region__c, pse__SOW_Hours__c, pse__Request_Priority__c, pse__Resource_Request_Name__c, pse__Resource_Role__c, pse__Start_Date__c, pse__Status__c, Partner_Requested__c, OA_Migration_ID__c, CurrencyIsoCode, Percent_Onsite__c) select (select [OwnerId] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'OwnerId' , case when case when p.custom_35 is null then ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else p.custom_35 end 'SOW Hours' and isnull(b.deleted,0) 1 --and mp.stage = 'Active'

We are currently migrating from DB2 to SQL Server. DB2 supports TIMESTAMP(6-12) while DATETIME2 only supports up to 7 decimals. Since we have many keys represented as TIMESTAMP(12) what would be the best way of migrating these to SQL Server? I was thinking we should store them as varchar(32) or char(32). This will allow storing all 12 fraction digits. I assume order by ascending/descending will work the same with varchar/char as a datetime2. I assume all SQL in the application can remain the same as datetime2 / timestamp values are represented as strings in SQL. We will however have issues using time functions in SQL Server unless we cast back and forth (which probably won't perform well). Any better suggestion?

I have this query that is giving me an error > The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Query: select COUNT(*) from dbo.patient INNER JOIN dbo.study on dbo.patient.pk = dbo.study.patient_fk and dbo.study.study_datetime IS NOT NULL and dbo.patient.pat_birthdate IS NOT NULL and dbo.study.study_datetime <= DATEADD(D, -2192, GETDATE()) and dbo.patient.pat_birthdate <= DATEADD(D, -7670, GETDATE()); I read where this might help to be converted to 'DATETIME2', but I can't quite figure out how to correctly convert the column 's.study_datetime' as this column more than likely has some bad values as entries.

I wrote a simple audit with triggers. when a trigger called, the data inside the trigger (inserted and deleted rows) are sent through broker to a procedure to write them in the Master Audit Table. here is the trigger: CREATE TRIGGER [dbo].[trg_personAudit] ON [dbo].[person] AFTER INSERT, UPDATE, DELETE AS DECLARE @auditBody XML = '' DECLARE @oldAuditBody XML = '' DECLARE @newAuditBody XML = '' DECLARE @DMLType CHAR(1) DECLARE @tableName sysname DECLARE @guid varchar(100) = NULL; BEGIN DECLARE @change_date datetime2(7) = sysdatetime(); SET @DMLType = 'I' IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) SET @DMLType = 'U'; ELSE IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) SET @DMLType = 'I'; ELSE SET @DMLType = 'D'; declare @userid nvarchar(20) select @userid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'userid=%' select top 1 @userid=splitdata from dbo.fnSplitString(@userid,'=') ORDER BY splitdata asc select @guid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'gid=%' select top 1 @guid=splitdata from dbo.fnSplitString(@guid,'=') ORDER BY splitdata asc IF (@userid IS NULL OR @userid = '') SELECT @userid = '-1' SELECT @oldAuditBody = (SELECT * FROM deleted FOR XML AUTO, BINARY BASE64, ELEMENTS) SELECT @newAuditBody = (SELECT * FROM inserted FOR XML AUTO, BINARY BASE64, ELEMENTS) SET @tableName = 'person' print 'generated guid is: ' + @guid; SELECT @auditBody = (select DB_NAME() as SourceDb, @tableName as SourceTable, @userid as UserId, @DMLType as DMLType, @oldAuditBody as OldData, @newAuditBody as NewData, @guid as gid, @change_date as ChangeDate for xml path('AuditMsg')) EXEC dbo.usp_SendAuditData @auditBody END GO EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'DELETE' GO EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'INSERT' GO EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'UPDATE' as you can see, I get the current time stamp in this line: DECLARE @change_date datetime2(7) = sysdatetime(); Suppose there is an insert and after that we have an update: sometimes this two triggers run exactly at the same time. even we have datetime2(7) but the times of the both rows are exactly the same. I can't find any solution to overcome this situation. Why the times are exactly equal?

I'm trying to restore a SQL Server database from a .bak file. The process completes without any issues, but the dates with datatype datetime2 are presented with totally different values (change to future dates, e.g. I see dates 1/1/4014). The source backed-up database collation is Cyrillic_General_CI_AS. The login user has been set to have English-us and Russian language but the same issue persists in both cases. Other types of data, e.g. float numbers, are ok (and readable), only dates are affected. What exactly is the issue here and can it be resolved? Is there a specific step we missed and we need to configure?

According to the documentation datetime2 (Transact-SQL) : > **Storage size** >6 bytes for precisions less than 3. 7 bytes for precisions 3 and 4. All other precisions require 8 bytes. The size of datetime2(0), datetime2(1), datetime2(2) use the same amount of storage (6 bytes). Would I be correct in saying that I might as well go with datetime2(2) and gain the benefit of the precision without any additional size costs? Please note: - This column is indexed with the PK to form a compound clustered index (used for table partitioning) - I do not care about milliseconds Would datetime2(0) be more cpu efficient when used in a where clause, or when seeking through an index? This is a massive table, so the smallest optimization will make a big difference.

So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've solved their problem, It came about that I don't actually know what the 0 turns into when used as they were using it. I originally thought it was closer to an integer overflow rather than a true conversion error, but that's not it. I tried it on a SQL 2016 box with DATEDIFF_BIG and same error. I have a sample for you guys below to play with along with what works and what doesn't. /** Setup The Sample */ DECLARE @TestValue DATETIME2(7) SET @TestValue = '0001-01-01 10:30:00.0000000' /** Conversion Error Msg 242, Level 16, State 3, Line 10 The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. */ SELECT DATEDIFF(MINUTE, 0, @TestValue) --Also does not work, same error. SELECT DATEDIFF_BIG(MINUTE, 0, @TestValue) /** Works */ SELECT DATEDIFF(MINUTE, '1/1/1900', @TestValue) /** Works */ SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue) /** Doesn't Work, you can't cast 0 to a DATETIME2 */ --SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME2), @TestValue) /** Works (or no error, which is fine)*/ SELECT DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME)) Bonus Question, since 0 doesn't work in all cases for DATETIME2, what's the alternative? **WHAT WE DECIDED TO DO** So, I have started recommending my team do the following, since you see 0 in lots of examples for datemath (first day of month, etc.). So I recommend that you do an explicit cast of 0 to datetime, then continue as you will. This will avoid the error while still working. So: DATEDIFF(MINUTE, CAST(DATETIME, 0), )

I need to calculate the actual time spent in the office per day. I have seen several examples online, but they all have TIME_IN and TIME_OUT in the same row. My application puts entries into a new row each time an "enter" or "exit" is recorded by the tracking device. I am able to calculate overall time per day for a "normal" day with the following query: use timelog; mysql> SELECT TIMEDIFF(MAX(date_time), MIN(date_time)) FROM timelog WHERE day_of_month="11"; +------------------------------------------+ | TIMEDIFF(MAX(date_time), MIN(date_time)) | +------------------------------------------+ | 09:06:00 | +------------------------------------------+ 1 row in set (0.00 sec) The data for all IN/OUT on the 11th: mysql> SELECT status,date_time FROM timelog WHERE day_of_month="11"; +--------+---------------------+ | status | date_time | +--------+---------------------+ | IN | 2017-04-11 08:39:00 | | OUT | 2017-04-11 09:42:00 | | IN | 2017-04-11 10:03:00 | | OUT | 2017-04-11 10:38:00 | | IN | 2017-04-11 10:43:00 | | OUT | 2017-04-11 12:09:00 | | IN | 2017-04-11 13:20:00 | | OUT | 2017-04-11 13:24:00 | | IN | 2017-04-11 13:26:00 | | OUT | 2017-04-11 14:06:00 | | IN | 2017-04-11 14:13:00 | | OUT | 2017-04-11 17:45:00 | +--------+---------------------+ Table: timelog Description: mysql> desc timelog; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | status | char(3) | NO | | NULL | | | date_time | datetime | YES | | NULL | | | day_of_week | char(9) | YES | | NULL | | | month | char(10) | YES | | NULL | | | day_of_month | int(2) | YES | | NULL | | | year | int(4) | NO | | NULL | | | hour | int(2) | YES | | NULL | | | minute | int(2) | YES | | NULL | | | time_of_day | time | YES | | NULL | | | SSID | char(15) | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ All Data for the 11th: mysql> SELECT * FROM timelog WHERE day_of_month="11"; +--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+ | status | date_time | day_of_week | month | day_of_month | year | hour | minute | time_of_day | SSID | +--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+ |IN | 2017-04-11 08:39:00 | Tuesday | April | 11 | 2017 | 8 | 39 | 08:39:00 | CGP Financial |OUT | 2017-04-11 09:42:00 | Tuesday | April | 11 | 2017 | 9 | 42 | 09:42:00 | CGP Financial |IN | 2017-04-11 10:03:00 | Tuesday | April | 11 | 2017 | 10 | 3 | 10:03:00 | CGP Financial |OUT | 2017-04-11 10:38:00 | Tuesday | April | 11 | 2017 | 10 | 38 | 10:38:00 | CGP Financial |IN | 2017-04-11 10:43:00 | Tuesday | April | 11 | 2017 | 10 | 43 | 10:43:00 | CGP Financial |OUT | 2017-04-11 12:09:00 | Tuesday | April | 11 | 2017 | 12 | 9 | 12:09:00 | CGP Financial |IN | 2017-04-11 13:20:00 | Tuesday | April | 11 | 2017 | 13 | 20 | 13:20:00 | CGP Financial |OUT | 2017-04-11 13:24:00 | Tuesday | April | 11 | 2017 | 13 | 24 | 13:24:00 | CGP Financial |IN | 2017-04-11 13:26:00 | Tuesday | April | 11 | 2017 | 13 | 26 | 13:26:00 | CGP Financial |OUT | 2017-04-11 14:06:00 | Tuesday | April | 11 | 2017 | 14 | 6 | 14:06:00 | CGP Financial |IN | 2017-04-11 14:13:00 | Tuesday | April | 11 | 2017 | 14 | 13 | 14:13:00 | CGP Financial |OUT | 2017-04-11 17:45:00 | Tuesday | April | 11 | 2017 | 17 | 45 | 17:45:00 | CGP Financial +--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+ Now for my question(s)... :-) How can I best calculate actual time spent in the office based on the time between each IN and OUT? I'd like to be able to mark the time between say "09:42" and "10:03" as time not worked, and the time between "10:03" and "10:38" as worked, and so on. I have tried negating all "IN" times except for the first of the day, and adding, but that didn't seem to work. Can someone help me craft a SQL statement (I am by no means even a novice) that will step through each day's IN/OUT timestamps and calculate "actual" time spent in the office? Since I am asking for the moon, I might as well ask for Mars too. As I am subject to on-call, it is not uncommon for me to work well past midnight, or as in the case of this morning, be called int the office from 01:00 to 02:45, so I'd like to be able to factor "past midnight" and "came in early, went home, came back in at 08:00" into the equation as well. If you need more information, or clarification on anything I have posted, please let me know. Your help is very much appreciated. Bryan

I have this stored procedure in SQL-Server 2014 USE [provbank] GO /****** Object: StoredProcedure [dbo].[GetHBSeconds] Script Date: 2018-10-05 08:40:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[GetHBSeconds] AS BEGIN DECLARE @hbdatetime datetime DECLARE @diffSec int SET @hbdatetime = (SELECT HBDateTime from HBData WHERE ID=1) SET @diffSec = datediff(second,@hbdatetime, convert(datetime,convert(char(19), getdate(),126))) SELECT @diffSec AS DiffInSeconds , @hbdatetime AS 'LastHB', convert(datetime,convert(char(19), getdate(),126)) AS 'CurrentTime' END And every now and then I get this error: > The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. What I have in the database is one row with a datetime column and an id. I have a separate program that writes in this row the current timedate like '2018-10-05 09:58:30.000'. And then I have the SP to calculate between current time and the timestamp to get # of seconds in difference between now and the timestamp. I don't think having the diff in seconds would be too narrow/too precise. Is there something I can change in my SP to avoid this error? Because the difference in the table and getdate() on the same machine/server shouldn't be more then a few seconds apart if all works, which it is, when I get this error.

We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but we have encountered few issues there. We managed to find one particular one where a query converting datetime to datetime2 and it returns NULL when it should have returned a value, not sure if it is CE or something? Please see the example below: CREATE TABLE TestCE ( Id INT IDENTITY(1,1), CurrentDateTime DATETIME ) GO INSERT dbo.TestCE(CURRENTDATETIME) SELECT GETDATE() UNION ALL SELECT '2018-04-11 08:44:42.643' UNION ALL SELECT '2018-04-12 09:49:45.334' GO SELECT * FROM TestCE --The resultset 1 2018-04-17 16:49:02.813 2 2018-04-11 08:44:42.643 3 2018-04-12 09:49:45.333 But when I run the following queries, don't get any result: SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333') GO SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643') GO SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813') I have also tried with QueryTraceOn option for backward CE but with no success: SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333') OPTION(QUERYTRACEON 9481); GO SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643') OPTION(QUERYTRACEON 9481); GO SELECT * FROM TESTCE WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813') OPTION(QUERYTRACEON 9481); Also set the following DB option but with no success either: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON As soon as I change the compatibility level back to 2008, queries start returning the data as expected.

I want to create a SQL Script to clean datetime2 field and set it up to datetime2 begin '1-1-0001 12:00:00'. How to set GETDATE() to '1-1-0001 12:00:00' instead of using convert function ? UPDATE XYZTable SET ReferralDate = CONVERT(datetime2, '1/1/0001 09:00')