At the beginning of my developer career, there was for me a problem with working with date and time or in combination with these. Later in my career, the next challenge comes up and I must work with international customers, so there are now timezones in the game.
Later on, I get more and more insights about the different datatypes for storing dates times, and both. So I will guide you through these datatypes and what they will be used for.
Available data types
First of all, I give you a glimpse of the available datatypes
Let's insert some test data into this table
The insert statement store the current date with the current time and current timezone in the desired columns.
DateTime
The DateTime column is one of the oldest known datatypes. When we select the value of this column it will result in this
You will see that there is the complete date and time stored, also the fractional seconds to the time value. The value will be represented in a 24-hour format. I think this is commonly used in Scenarios like time clock applications. Please pay attention, that these datatype use a store of 8 Bytes for a single value. You must keep this in mind when you design your database.
I will now use this column for comparison of the next datatypes, to show you the differences.
SmallDateTime
The next one is the DataType SmallDateType:
The smalldate time round (up or down) is the time value to the next second value because it does not store the fractional seconds. So, in fact, the smalldatetime consumes less space in your database, but you will lose then the fractional seconds. The fact that it will not store the fractional seconds will result in the storage use because this datatype consumes then only 4 bytes.
Scenarios for using the smalldatetime datatype are timestamps for orders or s.th. else, where it is not necessary to store the ms for each entry.
Date
The date datatype will only store, yes the name is yelling at you, the date value only
Every developer I know uses the CONVERT() function from SQL Server to get only a date value out of the datetime value. Instead of doing this and wasting CPU time and database space, you can use the date column for this. Yes, you heard it right, wasting of database space, because the date datatype only consumes 4 bytes. For comparison, the datetime datatype consumes 3 bytes.
Scenarios for date datatypes are contracts and birthdays.
DateTime2
Yep new datetime datatype, so let's first see the results from this datatype
By contrast to the new date data type, the datetime2 data type extends the granularity of the original datetime data type to 100 nanoseconds. Storage requirements depend upon the precision of the column, which is declared upon creation. Precision can be any value between 0 and 7, with the default being 7.
This data type is not very well documented about consumption through using it because it is very dynamic. I vary from 6 bytes (with the precision of 3) and 8 Bytes (with precision of 5).
So in my opinion these datatype will be used for time series applications.
DateTimeOffset
Let's now look at the datatype datetimeoffset:
This datatype is useful when you must handle different timezone. This datatype comes up with SQL Server 2008, and before it was hard to standardize times across timezones. You can now set the timezone from your client into the value too. So as you see in the screenshot the GETDATE() function will not include an offset.
Let's assume you have a company that processes some shippings, your company is using globally the Greenwich Mean Time.
Now if you enter a value to the Table that was made in the Easter time zone in the US, the insert statement look like this
So the cost for this storage will be 4 bytes more than the datetime column. You will then be able to store the timezone as well.
Time
Yes it's yelling at you, this datatype will store only the time not the date
The consumption relates to the precision defined on the column creation
It varies from 3 Bytes (precision of <3) to 5 Bytes (precision of >4)
As you can see from our example, we created this particular col_time column as time(7) (the default). The GETDATE() function returns values as a datetime data type, which has a precision of 3. The behavior of the time data type is as such that it will not truncate trailing zeros. This is standard behavior across all date and time data types in SQL Server.
Final words
You must understand your data and make the proper decisions when creating the fields in your databases so as not to waste valuable storage space throughout long- term data storage. Also, you must consider avoiding unnecessary conversions to display the user a date only and storing the unused time too.
Top comments (0)