Sukanya

This article will help us to learn new date time data types of SQl 2008 and their functions.

Why I need to separe Date & Time data type

In sql 2005 to store data & time we have data type called datetime .In order to store date OR time we go for date time data type Suppose we are inserting dataof bearth in to our database in this case we don’t need time but still we are declaring our data type into datetime and we are ignoring time and considering date. And if we have to insert time into our database we are still considering our variable in to datetime and ignoring date. so our datetime datatype in sql 2005 is not optimized

New Date & Time date type:

So in sql 2008 we have optimized date and time data type  and out datetime datatype is separated

These are the new four date time data types in sql 2008.

Date: It is used to store only date not time.and the range of date we can insert is 0001-01-01 to 9999-01-01 this is actually a large range.

Time: It is used to represent only time till 100^th nano Second user can specify time.

Datetimeoffset: It is used to represent date & time and we can also represent time zone also .suppose if the user is inserting Us timeing he can specify it as -6.00 as offset

Datetime2: It is used to represent date and time not time zone and user can specify precisions upto 100^th nono second and we can have optional parameters upto 0 to 7.

Summary of new Date & Time date type:

So now we are having detailed support for date time, larger fractional precision and large range of date we can have and finally time zone also we can represent.