DateTime vs DateTime2 and conversion exception

convert datetime2 to datetime datetime vs datetime2 vs datetimeoffset sql server 2008 datetime2 sql server time datatype sql server date data type sql server time only sql server datetime range sql 2008 data types

DateTime2 is new data type introduced in SQL Server which is nothing but an extension of existing DateTime data type. Question is why we are getting error of 'DateTime vs DateTime2 conversion exception' while we are not using an old version of SQL Server say 2005 or 2008. We will check this in this article and other issues which we face time to time and how to fix them. Let's first see the most common thing about DateTime and DateTime2 which will help to understand the issue.

DateTime:

  • Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
  • Default value: 1900-01-01 00:00:00
  • Range: January 1, 1753, through December 31, 9999

Syntax and Usage

syntax: datetime 

DECLARE @MyDatetime datetime
CREATE TABLE Table1 ( Column1 datetime )

No way to define the user-specified precision

DateTime2:

  • 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.

  • Default value: 1900-01-01 00:00:00

  • 0001-01-01 through 9999-12-31 or
  • January 1,1 AD through December 31, 9999 AD

Syntax and Usage

syntax: datetime2 [ (fractional seconds precision) ]

DECLARE @MyDatetime2 datetime2(7)
CREATE TABLE Table1 ( Column1 datetime2(7) )

Now have feature to define user-specified precision

DateTime vs DateTime2 conversion exception

Let's try to understand the different situation whne we can get this error, say we have a student class with with following properties

  public class Student
  {
    public int ID { get; set; }
    public string StudentName { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateUpdated { get; set; }
  }

Note, DateCreated and DateUpdated both are not nullable, when we will try to add the record, first time we will not pass DateUpdate value because it is the first time we adding.

Since this property is not nullable so automatically form C# it will use 01/01/0001.

We already not this range belongs from DateTime2 and not to DateTime so immediately we will get error of conversion from DateTime2 to DateTime.

This will happen even if our table is DateUpdated columns is nullable, because we are passing a value rather than null.

  1. Say all our property is nullable and table columns are also nullable:

If we are deciding the date on the basis of condition and none of the condition meat, see

DateTime myDate;
swith(xys)
{
  case x:
    myDate = DateTime.Now; break;
  case y:
    myDate = '01/01/2015'; break;       
}

If none of the case match then my date will not be assigned and default it will take again 01/01/0001 and same error.

See the complete detail DateTime and DateTime2

Alicia Gonzalez I am working on .Net since last 7 years.C# is really amazing language and I like to learn everything about C# and want to share whatever I know.
By Alicia Gonzalez On 11 Sep, 15  Viewed: 318

Other blogs you may like

What is XML and it's usage?

XML (Extensible Markup Language) is a simple text-based format for representing structured information: documents, data, configuration, books, transactions, invoices, and much more. It was derived from an older standard format called SGML. **XML Used For** XML is one of the most widely-used... By Alicia Gonzalez   On 25 Dec 2012  Viewed: 278

JavaScript getAttribute() Method

In JavaScript getAttribute() method is used to get the value of an attribute in an object. **Syntax:** object.getAttribute(attributename) **Parameter:** It takes only one parameter *attributename* **Example:** Suppose you have an anchor tag with following attributes <a... By Alicia Gonzalez   On 02 Dec 2012  Viewed: 421

jQuery and chexkbox

Whenever I need to use jquery with checkbox different functionality I always search for different sites to get my answer so I decided to write a small article to keep all the jquery functionality with checkbox at a single place, so trying to keep everything here. ***To get a checkbox on a page... By Ali Adravi   On 16 Nov 2012  Viewed: 535

Conditional where clause in SQL server and tricks

*Suppose you want to search the records on the basis of start and end date and for that if ModifiedDate is not null then use it otherwise use the Creation date:* Simply we can use, if in where clause like this If ModifiedDate IS NULL Where CreationDate Between @StartDate And... By Ali Adravi   On 14 Jul 2012  Viewed: 506

Prevent SQL Injection in your application

I found many web sites where easily inject the SQL and logged in there, so what is wrong with these web site code. Are they not authenticating the user information? They are, so how can anyone can logged in without creating the account? Let’s say you have written code to authenticate the user... By A K Sinha   On 14 Jul 2012  Viewed: 379