Questions tagged [datetime2]
The datetime2 tag has no usage guidance.
15
questions
57
votes
6
answers
69k
views
How to combine date and time to datetime2 in SQL Server?
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 ...
- 83.3k
22
votes
1
answer
22k
views
datetime2(0) vs datetime2(2)
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 ...
- 769
15
votes
1
answer
2k
views
Incorrect comparison of datetime and datetime2
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-...
- 1,451
6
votes
3
answers
2k
views
Datetime2 rounding issue
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 ...
- 165
4
votes
1
answer
3k
views
What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?
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 ...
- 8,611
4
votes
1
answer
205
views
Unable to restore database with datetime2(0) in Partition Function
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 ...
- 43
3
votes
2
answers
859
views
12 fraction digits in SQL Server
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 ...
- 165
3
votes
1
answer
866
views
SQL Server 2016 Compatibility Level and Datetime 2
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 ...
- 190
3
votes
2
answers
5k
views
Calculate total hours per day based on "TIME_IN" data in one row and "TIME_OUT" data on the next row
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 ...
2
votes
1
answer
6k
views
Help with out-of-range value
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
...
- 25
0
votes
1
answer
922
views
The datediff function resulted in an overflow. But should not
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 ...
0
votes
1
answer
2k
views
How to set datetime2 to mandate?
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 ...
- 245
0
votes
1
answer
75
views
Why some triggers run exactly at the same time?
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. ...
- 121
0
votes
0
answers
132
views
SQL Server database restore from backup changes all dates with datetime2 type return with values to future dates
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 ...
-3
votes
1
answer
27k
views
Operand type clash: int is incompatible with datetime2
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 ...
