All about Date Time Data Type

All about Date Time Data Type

Introduction

As I find many of the developer has a little bit confusion related to DATE and TIME data type of MS SQL Server. Here in this article I am trying to describe them all. Hope it will be helpful to understand them and clarify the knowledge.

In this article I am trying to discuss about data type

1.    DATETIME

2.    SMALLDATETIME

3.    DATE

4.    TIME

5.    DATETIME2

6.    DATETIMEOFFSET

DATETIME

This data type contains both DATE and TIME.

Date range	January 1, 1753, through December 31, 9999
Time Range	00:00:00 through 23:59:59.997
Element Range	YYYY is four digits from 1753 through 9999 that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Storage	8 Bytes

If we not specified the TIME in DATETIME data type it always 00:00:00.000. Lets taken an example of it

DECLARE @v_date DATE;

DECLARE @v_datetime DATETIME;

SET @v_date = '12-21-05';

SET @v_datetime = @v_date;

SELECT @v_datetime AS '@v_datetime', @v_date AS'@v_date';

Output

@v_datetime             @v_date

----------------------- ----------

2005-12-21 00:00:00.000 2005-12-21

If we set only the TIME component and NOT the DATE part, the DATE part will take 1900-01-01 by default.

DECLARE @v_time TIME;

DECLARE @v_datetime DATETIME;

SET @v_time = '12:10:05.1237';

SET @v_datetime = @v_time;

SELECT @v_datetime AS '@v_datetime', @v_time AS'@v_time';

@v_datetime             @v_time

----------------------- ----------------

1900-01-01 12:10:05.123 12:10:05.1237000

SMALLDATETIME

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Date range	January 1, 1900, through June 6, 2079
Time Range	00:00:00 through 23:59:59
Element Range	YYYY is four digits, ranging from 1900, to 2079, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.
Storage	4 Bytes

Here is an example

DECLARE @v_smalldatetime SMALLDATETIME;

DECLARE @v_date DATE;

SET @v_smalldatetime = '1955-12-13 12:43:10';

SET @v_date = @v_smalldatetime;

SELECT @v_smalldatetime AS '@v_smalldatetime',

       @v_date AS '@v_date';

@v_smalldatetime        @v_date

----------------------- ----------

1955-12-13 12:43:00     1955-12-13

DATE

Define only DATE not TIME.

Date range	January 1, 1 A.D. through December 31, 9999 A.D.
Element Range	YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represent a day of the specified month.
Storage	3 Bytes

DECLARE @v_date  DATE,

        @v_date1 DATE;

SET @v_date  = '1955-12-13';

SET @v_date1 = '1955-12-13 12:10:05.1237';

SELECT @v_date AS '@v_date', @v_date1 AS '@v_date1';

@v_date    @v_date1

---------- ----------

1955-12-13 1955-12-13

Look here, in variable @v_date1 we specified time also but it just removes the TIME part and shows us only the DATE part.

TIME

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

Range	00:00:00.0000000 through 23:59:59.9999999
Element Range	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
Storage	5 Bytes

Example

DECLARE @v_timeFrom TIME(4);

DECLARE @v_timeTo   TIME(3);

SET @v_timeFrom = '12:34:54.1237';

SET @v_timeTo   = @v_timeFrom;

SELECT @v_timeTo AS 'v_time(3)',

       @v_timeFrom AS 'v_time(4)';

v_time(3)        v_time(4)

---------------- ----------------

12:34:54.124     12:34:54.1237

DECLARE @time4 TIME(4);

DECLARE @time7 TIME(7);

DECLARE @timeC TIME; 

SET @time4 = '12:32:51.1234';

SET @time7 = '23:32:51.1234567';

--will only take the time and second fraction as 7 

SET @timeC = '1955-12-13 19:21:55.123'

SELECT @time4 AS 'time(4)',

       @time7 AS 'time(7)',

       @timeC AS 'timeC';

time(4)          time(7)          timeC

---------------- ---------------- ----------------

12:32:51.1234    23:32:51.1234567 19:21:55.1230000

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.

Date Range	January 1,1 AD through December 31, 9999 AD
Time Range	00:00:00 through 23:59:59.9999999
Element Range	YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year. MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year. DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month. hh is a two-digit number, ranging from 00 to 23, that represents the hour. mm is a two-digit number, ranging from 00 to 59, that represents the minute. ss is a two-digit number, ranging from 00 to 59, that represents the second. n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds.
Storage	6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

Example

DECLARE @v_datetime2  DATETIME2(4);

DECLARE @v_date       DATE;

SET @v_datetime2 = '12-10-25 12:32:10.1234';

SET @v_date      = @v_datetime2;

SELECT @v_datetime2 AS '@v_datetime2',

       @v_date AS '@v_date';

@v_datetime2           @v_date

---------------------- ----------

2025-12-10 12:32:10.12 2025-12-10

DATETIMEOFFSET

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Date Range	January 1,1 A.D. through December 31, 9999 A.D.
Time Range	00:00:00 through 23:59:59.9999999
Offset Range	-14:00 through +14:00
Element Range	YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Storage	10 bytes, fixed is the default with the default of 100ns fractional second precision.

Example

DECLARE @v_datetimeoffset datetimeoffset(4);

DECLARE @v_time           time(3);

DECLARE @v_date           date;  

SET @v_datetimeoffset = '12-13-25 12:32:10 +05:30'; 

SET @v_time           = @v_datetimeoffset;

SET @v_date           = @v_datetimeoffset;   

SELECT @v_datetimeoffset AS '@v_datetimeoffset',

       @v_date AS '@v_date',

       @v_time AS '@v_time'; 

@v_datetimeoffset                  @v_date    @v_time

---------------------------------- ---------- ----------------

2025-12-13 12:32:10.0000 +05:30    2025-12-13 12:32:10.000

MS SQL Server Version wise

Data Type	2000	2005	2008	2008 R2	2012
time	N	N	Y	Y	Y
date	N	N	Y	Y	Y
smalldatetime	Y	Y	Y	Y	Y
datetime	Y	Y	Y	Y	Y
datetime2	N	N	Y	Y	Y
datetimeoffset	N	N	Y	Y	Y

Hope you like it.

Understanding the SQL Server FILESTREM

Understanding the SQL Server FILESTREM

Introduction

Storing and managing data is always the first priority for a developer. Before MS SQL Server 2008 there are two ways that we fallow to sore and mange the unstructured data.

One approach to store unstructured data is in VARBINARY or INAGE columns. Although it ensures the transactional consistency and reduces data management complexity but performance wise it is very bad in nature.

 Second approach is store the unstructured data as disk files and stores the location of the file in the table along with the other structured data linked to it. The approach is quite good in terms of performance is concern. But it does not always guarantee the transnational consistency as we can remove the physical file without actually deleting the records from table.

MS SQL Server 2008 gives us a new approach to work with FILESTREAM. In this article I am trying to discuss related to it.

What us FILESTREAM

MS SQL Server 2008 introduces the FILESTREAM to work with unstructured data. It allows us to store unstructured data in NTFS file system and mange the transnational constancy with structured data stored in our table object.

How to Install FILESTRAEM Feature

It is a part of MS SQL Server 2008 Installation. By default the FILESTREAM feature is disabling in nature. We have to enable it when installing MS SQL Server 2008.

If we are not enabling the feature of FILESTRAEM we can do it after installation of MS SQL Server 2008.

Step-1

Go to the MS SQL Server 2008 SQL Server configuration Manager and select the Instance of the SQL Server. Then right click it and select property.

Step-2

From Property dialog Select the tab FILESTREAM and click on check box of ENABLE FILESTREAM for Transact - SQL access.

Setting the FILESTREAM Access Level

We have to set the access level of FILESTREAM by using SQL Server Management studio.

EXEC sp_configure filestream_access_level, 2

GO

RECONFIGURE

GO

Using FILESTREAM

To use the FILESTREAM we have to create the FILESTREAM enabled database. The FILESTREAM enabled database contains the flowing storage.

1.    MDF file

2.    LDF file (LOG File)

3.    FILESTREAM Data Container

CREATE DATABASE TEST_DB

ON

PRIMARY

(

   NAME = TESTDB,

   FILENAME = 'E:\SQL Server\TesDB.mdf'

), FILEGROUP TestDBFS CONTAINS FILESTREAM

      (

         NAME = TESTDBFS,

         FILENAME = 'E:\SQL Server\TestDBFS'

      )

LOG ON

   (                       

      NAME = TESTDBLOG,

      FILENAME = 'E:\SQL Server\TesDBLOG.ldf'

   )

GO

Root folder where FILESTREAM data of a database is stored is the FILESTREAM Data Container.

When we create a database with FILESTREAM feature enabled, the FILESTREAM Data Container will be created with the path we specify in your CREATE DATABASE statement.

CREATE TABLE object with FILESTREAM enabled columns

The file stream attribute enabled columns have data type VARBINARY(MAX) data type.

IF OBJECT_ID(N'dbo.tbl_ITEMMASTER', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_ITEMMASTER];

   END

CREATE TABLE [dbo].[tbl_ITEMMASTER]

(

   [ITEMID]    UNIQUEIDENTIFIER ROWGUIDCOL NOT NULLUNIQUE,

   [ITEMCD]    VARCHAR(20),

   [DESCR]     VARCHAR(50),

   [ITEMIMAGE] VARBINARY(MAX) FILESTREAM NULL

);

GO

It is important that if we use the FILESTREAM there should be one UNIQUEIDENTIFIER columns with ROWGUIDCOL and UNIQUE attributes.

Inserting Data into FILESTREAM

Now we are going to insert some data on our table object.

We have 2 product called Tooth Brush and Tooth Paste. The Images is located on the folder called Item in our local system (C:\Users\aa\Pictures\Item)

-- Declare a variable to store the image data

DECLARE @v_img AS VARBINARY(MAX)

-- Load the image data

SELECT @v_img = CAST(bulkcolumn AS VARBINARY(MAX))

 FROM OPENROWSET(

 BULK

   'C:\Users\aa\Pictures\Item\ToothBrush.jpg',

   SINGLE_BLOB ) AS x

          

-- Insert the data to the table         

INSERT INTO [dbo].[tbl_ITEMMASTER]

       ([ITEMID], [ITEMCD], [DESCR], [ITEMIMAGE]) 

SELECT NEWID(), 'ITEM101','Tooth Brush', @v_img;

Accessing FILESTREAM data

SELECT * FROM [dbo].[tbl_ITEMMASTER];

ITEMID          ITEMCD    DESCR          ITEMIMAGE

49A0F158-...    ITEM101   Tooth Brush    0xFF..

T-SQL code does not really do any manipulation of the FILESTREAM data except for saving and retrieving it.

It will be the client applications that actually use the FILESTREAM data.

Hope you like it.

datetimeoffset (T-SQL)

datetimeoffset (T-SQL)

Introduction

A lot of query came to me reading datetimeoffset in MS SQL 2008.

This is a special data type introduced by MS from MS SQL Server Version 2008 and onwards. Previously we used datetime and now it added another keyword in the name of Offset.

It basically

Date  +  Time  + Offset

The Datetime takes Date and Time portions and Offset takes the Time Zone.

In Microsoft Development Network

"Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock."

Syntax

datetimeoffset [ (fractional seconds precision) ]

How to Use it

DECLARE @MyDatetimeoffset datetimeoffset(7)

CREATE TABLE Table1 

( Column1 datetimeoffset(7) )

Default Format

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

Date Range

0001-01-01 through 9999-12-31

January 1,1 A.D. through December 31, 9999 A.D.

Time Range

00:00:00 through 23:59:59.9999999

Time Zone Offset Range

-14:00 through +14:00

Element Range

YYYY is four digits, ranging from 0001 through 9999, that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.

hh is two digits that range from -14 to +14.

mm is two digits that range from 00 to 59.

Example

SELECT 

CAST('2014-01-11 12:35:29. 1234567 +12:15' AS time(7))AS 'time',

CAST('2014-01-11 12:35:29. 1234567 +12:15' AS date) AS'date',

CAST('2014-01-11 12:35:29.123' AS smalldatetime) AS'smalldatetime',

CAST('2014-01-11 12:35:29.123' AS datetime) AS'datetime',

CAST('2014-01-11 12:35:29.1234567+12:15' ASdatetime2(7)) AS 'datetime2',

CAST('2014-01-11 12:35:29.1234567 +12:15' ASdatetimeoffset(7)) AS 'datetimeoffset',

CAST('2014-01-11 12:35:29.1234567+12:15' ASdatetimeoffset(7)) AS 'datetimeoffset IS08601';