45310

“Out of range” error converting datetime2 to datetime

Question:

I have a table (table1) with StartDate, StartTime, EndTime, all datetime2 and an OverMidnight boolean which specifies if the EndDate will be different (+1) from the StartDate.

I need to move these to another table (table2) with the columns StartDate and an EndDate, both need to be the datetime type, and use StartDate with StartTime to get the new StartDate and StartDate, OverMidnight, and EndTime to get the EndDate.

No matter what I have tried it gives me an error:

<blockquote>

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

</blockquote>

Answer1:

<a href="http://msdn.microsoft.com/en-gb/library/ms187819.aspx" rel="nofollow">DATETIME</a> and <a href="http://msdn.microsoft.com/en-us/library/bb677335.aspx" rel="nofollow">DATETIME2</a> have different acceptable date ranges.

DATETIME:

<blockquote>

January 1, 1753, through December 31, 9999

</blockquote>

DATETIME2:

<blockquote>

0001-01-01 through 9999-12-31

</blockquote>

I would check whether you have any dates in your original data out of the DATETIME range or whether your conversion code is creating dates outside the DATETIME range in error.

Recommend

  • KRL RSS parser: Handle encoding issues?
  • Websocket interval: performance problems on mobile devices?
  • Simple way to get more sensible colors in gscatter
  • How to acces variable in arrayadapter getView from onItemClick
  • Foreign key pointing to Compound Key
  • Is the scope of a variable initialized in a for loop declaration actually more than just block scope
  • Looping SQL statement insert
  • how to find the index position of the ARRAY Where NSPredicate pick the value. I use filteredArrayUsi
  • View Azure WebJob Schedule in Portal?
  • Java: List the dates between a start and end date [duplicate]
  • sql1 to get names and sql2 to get Impressions, I need to sort names by Impressions
  • have mysql select statement return fully qualified column names like table.field
  • LINQ to NHibernate WHERE EXISTS IN
  • SharePoint Designer 2010 - Determine if today's date is within x days of a start date column us
  • LINQ to Entities does not recognize the method 'Int64 Max(Int64, Int64)' method, and this
  • Java Date object constructor for getting string is deprecated
  • Migrating row from one table to another with conditions [MYSQL]
  • Can you build a truly RESTful service that takes many parameters?
  • Get the last date of each month in a list of dates in Python
  • What is this strange character in chrome's resource css viewer?
  • How to calculate networkdays minus holidays between 2 dates
  • Java : How to tint this PNG programmatically?
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • Angular2 - Template reference inside NgSwitch
  • HttpListener.IsSupported is false on XP SP3
  • Primefaces :radioButton inside a ui:repeat
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Extract zip entries to another Zip file
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • jQuery ready not fired after rails link_to is clicked
  • ActiveRecord query for a count of new users by day
  • How to rebase a series of branches?
  • Illegal mix of collations for operation for date/time comparison
  • How would I use PHP exceptions to define a redirect?
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Binding checkboxes to object values in AngularJs
  • How can I use threading to 'tick' a timer to be accessed by other threads?