Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [datetime2]

17 questions
57
votes
6 answers

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 23:59:59.9999999'? Some things which don't work are…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
22
votes
1 answer

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 datetime2(0), datetime2(1), datetime2(2) use the same…
Zapnologica
  • 779
  • 4
  • 9
  • 19
15
votes
2 answers

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-11-09 13:52:29.187' declare @LastSelectedDate_1…
Artashes Khachatryan
  • 1,533
  • 1
  • 12
  • 23
6
votes
1 answer

What does 0 in DATEDIFF(MINUTE, 0, ) 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 solved their problem, It came about that I don't…
Jonathan Fite
  • 9,414
  • 1
  • 25
  • 30
6
votes
3 answers

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 datetime2(6) column in MSSQL however MSSQL rounds the…
Kelvin Wayne
  • 165
  • 1
  • 8
4
votes
3 answers

Integer number in the 700000s as the days from year 1: how can this be cast in tsql to a date and back if the oldest datetime date is 1753-01-01?

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 ->…
questionto42
  • 366
  • 1
  • 2
  • 12
4
votes
1 answer

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 can successfully recover data from a READ WRITE…
Oleg
  • 43
  • 2
3
votes
1 answer

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 we have encountered few issues there. We managed…
Sajid
  • 310
  • 1
  • 2
  • 11
3
votes
2 answers

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 row each time an "enter" or "exit" is recorded by…
Bryan Moorehead
  • 31
  • 1
  • 4
3
votes
2 answers

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 best way of migrating these to SQL Server? I was…
Kelvin Wayne
  • 165
  • 1
  • 8
2
votes
1 answer

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 INNER JOIN dbo.study on dbo.patient.pk =…
Docjay
  • 25
  • 1
  • 4
0
votes
2 answers

Datetime2 in SQL 2014 Vs SQL 2022

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…
Meera K
  • 81
  • 7
0
votes
1 answer

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. here is the trigger: CREATE TRIGGER…
mostafa8026
  • 121
  • 6
0
votes
0 answers

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 future dates, e.g. I see dates 1/1/4014). The…
user2719567
  • 1
  • 1
0
votes
1 answer

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 QUOTED_IDENTIFIER ON GO --…
Stefan Grönberg
  • 1
  • 1
1
2