Stack Overflow
Stack Overflow

Questions tagged [datetime2]

A SQL Server type that defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

A SQL Server data type that define a date that is combined with a time of day that is based on a 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

References and resources

155 questions
880
votes
17 answers

DateTime2 vs DateTime in SQL Server

Which one: datetime datetime2 is the recommended way to store date and time in SQL Server 2008+? I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use…
Mikeon
  • 10,499
  • 6
  • 27
  • 31
118
votes
8 answers

Using SQL Server 2008 and SQL Server 2005 and date time

I've built a entity framework model against a 2008 database. All works ok against the 2008 database. When I try to update the entity on a 2005 database I get this error. The version of SQL Server in use does not support datatype 'datetime2 I…
Monroecheeseman
  • 2,011
  • 3
  • 18
  • 15
20
votes
3 answers

How do I calculate interval between two datetime2 columns (SQL Server)?

Hi I'm trying to calculate the difference between two columns of datetime2 type. However SQL server (2012) doesn't seem to like the following: select cast ('2001-01-05 12:35:15.56786' as datetime2) - cast ('2001-01-01 23:45:21.12347' as…
Kevin Tianyu Xu
  • 646
  • 2
  • 8
  • 15
10
votes
2 answers

Is there a function like isdate() for datetime2?

I know there is a function called ISDATE to validate DATETIME columns, but it works only for the SMALLDATETIME and DATETIME types. Is there a similar way to validate the new data type DATETIME2 in SQL Server 2008 and 2012?
Vladimir
  • 196
  • 3
  • 3
  • 12
9
votes
2 answers

Is number after DateTime2 is for nanosecond purpose?

I have read the document already but i do not fully understand The default precision is datetime2(7) is 7 represent the precision of nanosecond?
Sarawut Positwinyu
  • 4,974
  • 15
  • 54
  • 80
9
votes
1 answer

How to update a DateTime on SQL Server preserving milliseconds using Delphi 7 ADOQuery.ExecSQL?

How do you update a DateTime on SQL Server 2012 preserving milliseconds using Delphi 7 ADOQuery.ExecSQL? SQL server seems to drop the seconds precision so I do not have milliseconds or microseconds on a SQL Server 2012 DateTime2[7] field. MS Access…
user610064
  • 481
  • 2
  • 11
  • 25
9
votes
1 answer

SQL Server 2008: Get date part from datetime2

I've been using this very handy method of extracting the date portion from a datetime value: select dateadd(day, datediff(day, 0, @inDate), 0) This basically zeroes out the time portion of the date. It's fast, and more importantly it's…
Trent
  • 1,089
  • 3
  • 12
  • 24
8
votes
4 answers

DateTime2 to C# DateTime with SqlDataReader

I realize this might be a dupe, but I've spent hours searching for the answer and can't seem to find it. I'm currently creating a web API that retrieves concert data. I have a SQL Server table that holds a start and end date, both as a datetime2…
Dennie
  • 743
  • 2
  • 13
  • 30
7
votes
2 answers

SqlDateTime overflow error when saving DateTime.MinValue on a POCO entity

I've been having som issues when saving POCO entities with a DateTime property. When the DateTime property has the value of DateTime.MinValue the SaveChanges() fails because of the difference between SqlDateTime.MinValue and DateTime.MinValue. So,…
Peter Wikström
  • 261
  • 4
  • 11
7
votes
2 answers

Code first in entity framework set column to type datetime2 in sql server

I have a value DateTime dt = DateTime.Parse("2015-10-12 14:24:40.582"); with it I do: SomeEntity someEntity = new SomeEntity() { ID = 1, ChangedOn = dt }; context.SomeEntities.Add(someEntity); What I found out: in database table the…
Developer Marius Žilėnas
  • 5,813
  • 1
  • 41
  • 70
7
votes
2 answers

Why does EF5 code first use datetime2 when inserting a nullable datetime into the database?

I am saving a Cart object to the database that has a nullable datetime. This is the error I get: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. There are quite a few stackoverflow posts…
Matt R
  • 2,577
  • 5
  • 30
  • 46
6
votes
0 answers

How to configure spring.data.convert.CustomConversion for MSSQL date

Recently I noticed that our spring-boot services are logging a warning from org.springframework.data.convert.CustomConversions. The message is this: Registering converter from class microsoft.sql.DateTimeOffset to class java.time.OffsetDateTime as…
Joel
  • 227
  • 3
  • 8
6
votes
1 answer

SQL Server 2016 : join DATETIME2(3) with DATETIME

I'm getting unexpected results when joining on a DATETIME2(3) and a DATETIME column with PK, in SQL Server 2016. I have the following table: CREATE TABLE DATETIME_TEST ( [DATETIME] DATETIME NOT NULL, [DATETIME2_3] DATETIME2(3) ); ALTER…
Ida Amit
  • 1,411
  • 2
  • 13
  • 27
6
votes
2 answers

Datetime2 conversion in EF4

well, after having spent endless time googling for an answer , i have to add another question in this matter... i have a table with 2 datetime fields, generated by EF4 EDMX designer in VS 2010. the generated fields in SQL 2008 are datetime and not…
user300435
  • 95
  • 5
6
votes
2 answers

How to compare datetime2 with datetime

I need compare property datetime2 that one database with property datetime that other database because I don't find method for convert datetime to datetime2. I test this sentence: select CAST(FechaAlta AS datetime) from tutors but return this error…
Raquel Rodriguez Gonzalez
  • 57
  • 1
  • 4
1
2 3
…
10 11