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
5
votes
2 answers

For the .NET DateTime type, why is the inferred database type SqlDbTypes.DateTime instead of SqlDbTypes.DateTime2?

For the .NET DateTime type, why is the inferred database type SqlDbTypes.DateTime instead of SqlDbTypes.DateTime2? (See http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx) Background By defaulting to the less-precise SQL DateTime type, the .NET…
Triynko
  • 18,766
  • 21
  • 107
  • 173
5
votes
1 answer

Changing the Dapper mapping for .net DateTime to use DbType DateTime2 and reinstating it back again

Similar to the question How can I get Dapper to map .net DateTime to DateTime2; but I want to be able to set it back again afterwards. The currently accepted answer to that question involves changing a Dapper source file; but I'm using the NuGet…
Richardissimo
  • 5,596
  • 2
  • 18
  • 36
5
votes
4 answers

SQL ignore nanoseconds when comparing datetime2

I have 2 tables with a datetime2 field each. One of the tables has stored nanoseconds as well, while the other one only stores up to milliseconds (there is no way to change this data). Now I want to compare the 2 tables and check if the datetime2…
XtremeBaumer
  • 6,275
  • 3
  • 19
  • 65
5
votes
1 answer

Accuracy of SYSDATETIME Data Type in SQL Server

I have done some testing using the SYSDATETIME in stored procedure in SQL Server 2008. I have setup a table with a datetime2(7) with a IDENTITY field. I understand the difference between the precision and the accuracy of this data type however, I…
League
  • 113
  • 1
  • 8
5
votes
3 answers

SQL select from multiple tables based on datetime

I am working on a script to analyze some data contained in thousands of tables on a SQL Server 2008 database. For simplicity sakes, the tables can be broken down into groups of 4-8 semi-related tables. By semi-related I mean that they are data…
mkolker
  • 53
  • 1
  • 3
5
votes
2 answers

Converting a table Colum from datetime2 to datetime

I have been assisgned with a starnge assigment where i need to convert 40 tables columns from datetime2 to datetime. this is the datetime format what i am having in my database.2007-11-12 00:00:00 it contains more than 90,000 records Please assist
Techiesyam
  • 63
  • 1
  • 4
5
votes
1 answer

Is it possible to force all DateTime properties to be modeled as DateTime2?

In Entity Framework 6 Code First, is there a way to force all DateTime properties to be modeled as DateTime2? I know that I can do .HasColumnType("datetime2") on each individual DateTime property, but I'm wondering if there's a way to set it as a…
Eric
  • 5,842
  • 7
  • 42
  • 71
4
votes
2 answers

How to work around EF 4.1, CodeFirst C# DateTime datetime2 vs. datetime incompatibility

My Code First models have System.DateTime properties. When the database seeding code is invoked, it throws this exception: SqlException (0x80131904): The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range…
UniqueMan
  • 56
  • 1
  • 5
4
votes
2 answers

Is there a reason not to use DateTime2?

Am I missing a reason not to use the new DateTime2 datatype? For example, might it cause problems when migrating to another database system or integrating it with another technology?
Sarawut Positwinyu
  • 4,974
  • 15
  • 54
  • 80
4
votes
2 answers

SQL Server smalldatetime vs datetime2

I've read several articles about using data types that support dates in SQL Server but I'm still undecided about what kind of data to use. All the people says to use the new and powerful datetime2 type but smalldatetime is still more convenient in…
Oscar Peli
  • 1,108
  • 2
  • 11
  • 18
4
votes
4 answers

T-SQL: Convert datatime2 to datetime for all columns of type datetime2

I've got a database full of datetime2 columns than needs to be moved to a SQL 2005 database. So, I need to convert all these datetime2(7) columns to datetime. How can I go about doing this? Right now I've managed to select the table name and column…
David Murdoch
  • 87,823
  • 39
  • 148
  • 191
3
votes
2 answers

SSIS 2008 Execute SQL output parameter mapping datetime2 problem

I'm trying to use an Execute SQL Task in SSIS 2008 to map a store procedure output parameter to a package variable. The package variable is SSIS type DateTime and the store procedure parameter is SQL type DATETIME. The SQL Statement is EXEC…
Tom Hunter
  • 5,714
  • 10
  • 51
  • 76
3
votes
1 answer

Datetime2 precision in .NET

Microsoft did introduce the Datetime2 type in SQL server 2008. However, even with VS 2010 (made by the same compagny and realeased 2 years after MSSQL2008), I couldn't find a variable type that would handle Datetime2 with full precision. Is there…
The_Black_Smurf
  • 5,178
  • 14
  • 52
  • 78
3
votes
5 answers

Concatenate date and string to create datetime in SQL

I need to concatenate a datetime and a time field in SQL to a single date time. e.g I have a datetime of 2017-09-05 00:00:00.000 and a string time of 11:00. What I want is a single field in a view of 2017-09-05 11:00:00.000 I have tried casting the…
SayCodeWell
  • 48
  • 1
  • 9
3
votes
2 answers

ssis script converts System.DateTime to DT_DBTIMESTAMP but does not convert to DT_DBTIMESTAMP2

when i try to pass DateTime.Now value to DT_DBTIMESTAMP field via this code public override void Input0_ProcessInputRow(Input0Buffer Row) { Output0Buffer.AddRow(); Output0Buffer.Dt = DateTime.Now; } it works well. but when Output0Buffer.Dt…
iliyesku
  • 361
  • 2
  • 10
1
2
3
…
10 11