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

DataColumn - how to distinguish datetime or datetime2

I am having trouble identifying the type of a datetime DataColumn. If I grab a DataTable from SQL 2008 a datetime DataColumn could be a datetime or datetime2 but there appears to be no difference when in C#. I need to validate data prior to loading…
Drammy
  • 940
  • 12
  • 30
0
votes
0 answers

Operand type clash: datetime2 is incompatible with int, when using convert NVARCHAR

I can't save this view because of the error. I'm not using cast because I thought it wasn't necessary. I'm new to T-SQL and am very unclear on how to track down the source of hte error since SSMS doesn't give any hints: SELECT TOP (100)…
efuddy
  • 105
  • 1
  • 3
  • 11
0
votes
0 answers

Code First, set GetDate() on DateTime2 field

I've followed the examples like accepted here: Possible to default DateTime field to GETDATE() with Entity Framework Migrations? In Sql server, the "Default Binding or Value" is now getting set to ('1900-01-01T00:00:00.000'), but not to GetDate() as…
VirtualLife
  • 402
  • 5
  • 14
0
votes
1 answer

Is there a "Try" equivelent of DATETIME2FROMPARTS?

Given the following fields: Y INT, --Year. M INT, --Month. D INT, --Day. T FLOAT --Hours (H), minutes (m), seconds (s) and milliseconds (x) (in the form HHmmss.xxx). Is there a way to attempt to convert these values into a DATETIME2 without having…
Matt Arnold
  • 668
  • 2
  • 8
  • 21
0
votes
1 answer

Entity Framework not working correctly with DateTime column when converted to compatibilty 130

The below code was working with SQL Server Compatibility Level 100. However updating to 130, starting making the code below fail. I believe it has to do with the datetime and precision. The item isnt deleted anymore. Any clue why and is there a…
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
0
votes
1 answer

How does one create a Deterministic Function returning a DATETIME2?

I have a table which nonsensically has DateTime values stored in Year (INT), Month (INT), Day (INT) and Time (FLOAT) columns; unfortunately I'm not allowed to change this but have a requirement to partition the table by Year and Month. Therefore, I…
Matt Arnold
  • 668
  • 2
  • 8
  • 21
0
votes
1 answer

SQL Server Export fails on DateTime2 => PostgreSQL

I am trying to export number databases from MS SQL to PostgreSQL. Unfortunately SQL Server Export tool fails to work with DateTime2 type columns. Everytime I am getting: Error 0xc020844b: Data Flow Task 1: An exception has occurred during data…
Mike
  • 187
  • 16
0
votes
0 answers

What are the dangers of converting all datetime columns in a database to datetime2

We have a database with over 100 tables which has already been deployed to tens of production sites. Initially all datetime columns were of type datetime, but as the system evolved we started to use datetime2 for new modules/features. We now…
PiotrS
  • 180
  • 3
  • 16
0
votes
1 answer

How can I have two date variables in datetime2 type in sql?

I want to insert these values into existing table. insert Schedule ([Title], MeetingTime) values (N'First meeting', ('2018-10-9 18:30:00' and '2018-11-13 18:30:00' )), (N'Second meeting', ('2018-11-6 18:00:00' and '2018-12-4 18:00:00'…
jayko03
  • 2,329
  • 7
  • 28
  • 51
0
votes
1 answer

Migrating to SQL Server 2017 : same query returns no rows sometimes

I've just upgraded to SQL Server 2017 from SQL Server 2014 (only dev environment yet) and noticed a very strange behavior : the same query has two different impacts on both servers. The query has been rewritten and reduced to the minimum for the…
pti_jul
  • 432
  • 1
  • 5
  • 18
0
votes
1 answer

Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value during migration EF 6

When applying a migration using "update-database" I get an error about conversion of datetime2 to datetime. How do I fix this?
Keith Harris
  • 1,118
  • 3
  • 13
  • 25
0
votes
3 answers

SQL Server - How to remove trailing zeros from milliseconds in datetime2 data type

How can I use any cast or convert function to remove trailing zeros from milliseconds in datetime2 data type. For example, I have these data: 2018-02-17 13:26:55.033000 2018-02-17 12:37:12.300000 2018-02-17 14:55:30.110000 I want to change it…
Katika Panichapong
  • 1
  • 3
0
votes
2 answers

Weird datetime and dateime2 millisecond comparison issue

I have been facing typical comparison issue with datetime and datime2. I am unable to understand why in 1 & 2 doesn't return equal result. I have read about how datetime value stored from msdn and based on that for 1 - it should have returned equal…
Punit
  • 1,347
  • 3
  • 20
  • 39
0
votes
0 answers

PayPal IPN SqlDateTime overflow

I am have been struggling to insert paypal IPN Data into my database. I keep getting the exception: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Here is the Stack Trace: at…
user2476451
  • 43
  • 5
0
votes
1 answer

How can I get the full milliseconds in SQL server?

DECLARE @Now DATETIME2(7) = SYSDATETIME(); SELECT [Datetime2(1)] = DATEPART(MILLISECOND,CAST(@Now AS DATETIME2(1))), [Datetime2(2)] = DATEPART(MILLISECOND,CAST(@Now AS DATETIME2(2))), [Datetime2(3)] = DATEPART(MILLISECOND,CAST(@Now AS…
Jamie Twells
  • 1,924
  • 4
  • 26
  • 56
1 2 3
…
10 11