Most commonly used SQL Server Data Types

In our previous blog, we have discussed the second SQL Server Data Types category i.e., Approximate Numeric Data Type.

In this blog, we are going to discuss the third category i.e., Date and Time Data Type.

III. Date and Time Data Types: - are those that accept date and time values.

The following table lists the Date and Time Data Types in SQL Server with minimum and maximum range value: -

 

Date and Time Data Types

Things to Remember: -

1. The time is based on a 24-hour day.

2. For ‘datetime’ data type,

(2.1) the storage size is 8-bytes.

(2.2) the default value is 1900-01-01 00:00:00

(2.3) during the cast and convert operation, the following changes take place.

(2.3.1) Conversion from date to datetime: the date, i.e. the year, month and day are copied, but the time component is set to default value, i.e. 00:00:00.000

(2.3.2) Conversion from time to datetime: the time, i.e. the hour, minute, second and fractional second are copied, but the date component is set to default value, i.e. 1900-01-01

(2.3.3) Conversion from smalldatetime to datetime: the date, i.e. the year, month, day and time, i.e. hour and minute are copied, but the time component i.e., second and fractional second is set to 0.

3. For ‘smalldatetime’ data type,

(3.1) the storage size is 4-bytes, fixed.

(3.2) the default value is 1900-01-01 00:00:00

(3.3) the second is always zero (:00) and without fractional seconds.

(3.4) during the cast and convert operation, the following changes take place.

(3.4.1) Conversion from date to smalldatetime: the date, i.e. the year, month and date are copied, but the time component is set to default value i.e., 00:00:00

(3.4.2) Conversion from time to smalldatetime: the time, i.e. the hour, minute are copied, but the date component is set to default value i.e., 1900-01-01

(3.4.3) Conversion from datetime to smalldatetime: the date, i.e. the year, month, date and time, i.e. hour and minute are copied, but the time component i.e., second and fractional second are rounded up.

4. For ‘date’ data type,

(4.1) the storage size is 3-bytes, fixed.

(4.2) the default value is 1900-01-01

(4.3) during the cast and convert operation, the following changes take place.

(4.3.1) Conversion from datetime to date: the date, i.e. the year, month and date are copied.

(4.3.2) Conversion from smalldatetime to date: the date, i.e. the year, month and date are copied.

(4.3.3) Conversion from time to date: we’ll get the result as “Operand type clash: time is incompatible with date”

5. For ‘time’ data type,

(5.1) the storage size is 5-bytes, fixed with the default of 100ns fractional second precision.

(5.2) the default value is 00:00:00

(5.3) during the cast and convert operation, the following changes take place.

(5.3.1) Conversion from datetime to time: the time, i.e. the hour, minute, second and fractional second are copied.

(5.3.2) Conversion from smalldatetime to time: the time, i.e. the hour, minute are copied and second fractional second are set to zero (0).

(5.3.3) Conversion from date to time: we’ll get the result as “Operand type clash: date is incompatible with time”

6. Storage space or the number of digits it may hold is a very important thing. You may understand this by reading the next two lines. If you unknowingly convert a ‘time’ type to ‘time(2)’ type, then the extra digits will be truncated, which is not good. This result in heavy data lost.

Ex-

DECLARE @time time = getdate(); 

DECLARE @time1 time(2) = @time;   

SELECT @time AS 'Time', @time1 AS 'Time1';

Output:

Time                            Time1

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

00:07:59.9270000       00:07:59.93

7. Choosing right data type for each column reduce backup time, improve SQL Server performance and improve the execution time for queries, views, joins.

Now the question comes to our mind. How to use these Data Type?

Let’s understand and learn how to use these data types in some real situation. The Situation is something like - where user creates his account on a system.

Practical Implementation:

For this, we will create a table, i.e., tbl_DateAndTimeDataTypes. In which, values are inserted into each column with respect to their column data type. After inserting the record successfully. We’ll also learn how to return or view those inserted records using a select statement. Just follow the below-mentioned steps:

Step-1: Open a New Query window in the SQL Management Studio instance.

Step-2: Create a table using create statement. As you can see in the create statement - I have used all the data type just to get an idea - How and where to use each and every type?

create table tbl_DateAndTimeDataTypes

(

LoginDate datetime,

AccoutCreatedDate smalldatetime,

DateOfBirth date,

LastLoginTime time

)

Step-3: Just press Execute button or F5 key to run that creates statement query. I am hoping you have written the exact query that I have mentioned. Once clicking the execute button you will get a message “Command(s) completed successfully.”

Step-4: Now you need to insert a record based on the column type. No problem, just copy the below code that I have created for you. Before copying, I would strongly encourage you to understand each and every column value. Now, Execute the insert query. You will get the message “(1 row(s) affected).” It implies that you are doing things in a right way.

insert into tbl_DateAndTimeDataTypes(LoginDate,AccoutCreatedDate,DateOfBirth,LastLoginTime)

values(GETDATE(), GETDATE(), GETDATE(), GETDATE())

Step-5: Lastly, view the inserted record by executing the select query.

select * from tbl_DateAndTimeDataTypes

 

Output Window

More Explanation On Table Column: -

1. LoginDate: We have used this column and its type, ‘datetime’ to record the user login activity (i.e., its date and time yyyy-mm-dd hh:mm:ss[.nnn]) in the system.

2. AccountCreatedDate: You can also use ‘smalldatetime’ type to record the date and time.

3. DateOfBirth: When your need is to store the date part only, then use ‘date’ type.

4. LastLoginTime: When your need is to store the time part only, then use ‘time’ type.

Note: n in [.nnn] denotes the fractional second.

Lastly, I always force coders to act smart while working with different data types. It’s not a good practice to use high precision data type every time.

For any query, comment us below.

By this, we learn how and where to use Date and Time Data Type in SQL Server.

Previous - Most commonly used SQL Server Data Types #3

Next - Most commonly used SQL Server Data Types #5

Related Questions: -

#newtoprgm try the first program

Q-1 What will be the output of the following code?

DECLARE @datetime datetime = getdate(); 

DECLARE @smalldatetime smalldatetime = @datetime;   

SELECT @datetime AS 'datetime', @smalldatetime AS 'smalldatetime';

A) datetime=2016-09-12 00:35:44.530 and smalldatetime=2016-09-12 00:36:00

B) datetime=2016-09-12 00:35:44.530 and smalldatetime=2016-09-12 00:35:00

C) datetime=2016-09-12 00:35:44.530 and smalldatetime=2016-09-12 00:35:44

D) datetime=2016-09-12 00:35:44.530 and smalldatetime=2016-09-12 00:36:44

Ans- Option (A).

Explanation: The fractional second are rounded up.

Q-2 Which one of the following is invalid Date and Time Data Types?

A) getdate()

B) datetime

C) date

D) smalldatetime

Ans- Option(A).

Explanation: GETDATE() or getdate() is a non-deterministic function, which retrieves the current date and time based on the clock settings on the local system on which the instance of SQL Server is running.

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS

Keep learning and sharing...