Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Working with dates and conversions in the SQL Server

In this article I will introduce the two types of data that the SQL Server has to work with values dates and time; how these types differ from one another as well as how they are stored; and how to work with these values.

Course:  

SQL Server

Working with dates and conversions in the SQL Server

 

This article examines:

This article uses the following technologies:

  • The types of DateTime and SmallDateTime data;
  • How the SQL Server stores the values of date and time;
  • Dates/Time in the SQL Server;
  • Functions and conversions.

SQL Server 2000.

 

A very common developers’ complaint is in the apparent difficulty in working with date and time types in the SQL Server. I say apparent, because the great truth is that to work with date and time in the SQL Server, the first thing to do is to understand how the values of the date/time type are stored.

It is true that, unfortunately, the SQL Server haven’t a type of data that is only date and another type that is only time. But, from the moment that you to understand how the SQL Server stores and treats this data, you will see that working with date and time in the SQL Server is a sufficiently simple process.

In this article I will introduce the two types of data that the SQL Server has to work with values dates and time; how these types differ from one another as well as how they are stored; and how to work with these values.

We will also see some internal functions of the SQL Server that simplify a great deal treating and working with dates in the SQL Server.

 

The data types DateTime and SmallDateTime

The SQL Server supports two data types to work with values date and time. They are: DateTime and SmallDateTime. The basic difference between these two is in the amount of bytes used for storage, the range of supported dates and its precisions.

While DateTime uses 8 bytes for storage, SmallDateTime uses only 4 bytes. It is for that reason that DateTime manages to store a greater range of dates and also possesses a higher precision than SmallDateTime.

DateTime stores dates from January, 1st, 1753 through December, 31st, 9999 with a precision of 3.33 milliseconds or 0.00333 seconds. The values are rounded off for increments of .000, .003 or .007 seconds, as shown in Table 1.

 

Example

Rounding Off

01/01/98 23:59:59.999

1998-01-02 00:00:00.000

01/01/98 23:59:59.995, 01/01/98 23:59:59.996,

01/01/98 23:59:59.997 or 01/01/98 23:59:59.998

1998-01-01 23:59:59.997

01/01/98 23:59:59.992, 01/01/98 23:59:59.993,

01/01/98 23:59:59.994

1998-01-01 23:59:59.993

01/01/98 23:59:59.990 or 01/01/98 23:59:59.991

1998-01-01 23:59:59.990

Table 1. Examples of rounding off with DataTime

 

Notice that the schedules with milliseconds between .990 and .991 were rounded off to .990. The values between .992 and .994 were rounded off to .993 and the values between .995 and .998 were rounded off to .997.

SmallDateTime stores dates from January, 1st, 1900 to June, 6th, 2079 with the precision of up to 1 minute. SmallDateTime values with 29.998 seconds or less are rounded off to the previous minute; values with 29.999 are rounded off to the superior minute. Some examples can be seen in Table 2.

 

Example

Rounding Off

2000-05-08 12:35:29.998

2000-05-08 12:35:00

2000-05-08 12:35:29.999

2000-05-08 12:36:00

Table 2. Examples of rounding off with SmallDataTime

 

Both the DateTime as the SmallDateTime represent the date and time as a value which is the same as the number of days passed since the midnight of January, 1st, 1900, known as the base date.

As described previously, SmallDateTime can only represent dates from this base date onward. Now, DateTime, can also represent dates that come before January, 1st, 1900. In order to do so, it stores the values as negative numbers.

DateTime values are stored internally by the SQL Server as being 2, 4 bytes, whole numbers (adding up to 8 bytes of storage). The first four store the number of days elapsed before or after the base date. The other four store the time of the represented day as 0.0033 seconds units after midnight.

SmallDateTime values are stored as being 2, 2 bytes, whole numbers (adding up to 4 bytes of storage). The first two store the number of days elapsed since January, 1st, 1900. The other two, store the number of minutes since midnight.

 

Understanding the storage of the dates and time values

To understand how the values date and time are stored in the SQL Server, divide the value date and time in two parts: the whole part and the fractional part. The whole part represents the number of days elapsed since the base date. The fractional part represents the part hours, elapsed since midnight of the base date.

In order for you to understand better, imagine that it midday, January, 4th, 1900. Internally, the value that represents this date and time is stored as 3.5.

In this in case, three represent the number of days since the base date and 0.5 represents the half of the day passed since midnight. To test it, execute the query that follows in the Query Analyzer:

 

SELECT CAST(CAST('1900-01-04 12:00' AS DateTime)

  AS Float)

 

In the example, we used the CAST function to convert the 1900-01-04 12:00 string into DateTime and, later, into Float. As a result we have the value 3.5 of the Float type representing the date.

If we use the same SELECT, passing as string, the date 2006-10-17 00:00, we will have as a result, the value 39005.5 where 39005 represents the number of days elapsed since the base date and 0.5, half of the day since midnight.

Unfortunately, neither the SQL Server 2000 nor the SQL Server 2005 supply data types which store only the date or just the time. In such case, the data types date and time cannot store the date without the time or the time without the date. As a result of this, if you store a date without informing the time part, the part that represents the time will be defined as 0.

This will be represented as midnight using the 00:00:00 format if you are using a field of the SmallDateTime type or 00:00:00.000 if you are using a DateTime field. Similarly, if you store only the time without informing a date, the part that represents the date will be defined as 0.

This will be represented as January, 1st, 1900 for both the data types. To better understand, execute the following code in Query Analyzer:

 

CREATE TABLE TB_DATE (Date DateTime,

  Time SmallDateTime)

INSERT TB_DATE VALUES ('2006-10-17','15:10')

INSERT TB_DATE VALUES ('11:00','2006-10-17')

SELECT * FROM TB_DATE

 

The example creates the TB_DATE table with the Dates (DateTime) and Times (SmallDateTime) columns. In the first INSERT, observe that for the Date column only the date part is informed and for the Time column, only the time part. In the second INSERT, the exact opposite process is performed. The SELECT in TB_DATE table brings the result in Figure 1.

 

Figure 1. Saving data in DateTime and SmallDateTime columns

 

Observe that for both the data types, for the dates informed without the time part, the SQL Server considered the time as being 0 or midnight. Now, for the dates without the date part, the SQL Server considered the date as being January, 1st, 1900. That is, the base date.

It is important to notice that the same happens when you make a SELECT in a date field. Observe the following example:

 

SELECT CAST('2006-10-17' AS DateTime),

  CAST('10:00' AS DateTime)

SELECT CAST('2006-10-17' AS SmallDateTime),

  CAST('10:00' AS SmallDateTime)

 

The result is shown in Figure 2.

 

Figure 2. Example of Select in a Date field Date

 

In the example, the first SELECT uses the CAST function to convert the string representing the date into the DateTime type, while the second converts into the SmallDateTime type.

Observe that the first CAST passes a date without informing the time part (2006-10-17), so the SQL Server understands that the time part is midnight or 0. The second CAST informs only the time part (10: 00), so the SQL Server understands that the date is January, 1st, 1900.

 

Working with the date part

Having understood how the SQL Server treats and stores the dates, we will then see how to perform research with dates in the SQL Server. For a better understanding, consider an orders’ table with the registers displayed in Figure 3.

 


Figure 3. Example data of the orders table

 

The script for creation of the table and population of the example data are found in Listing 1.

 

Listing 1. Script for creation and population of the orders table

CREATE TABLE TB_ORDER(

  OrderID int,

  CustomerID VarChar(10),

  OrderDate DateTime,

  SendDate SmallDateTime)

GO

INSERT TB_ORDER VALUES (1,'NILTON',

  '2005-02-28 10:00:00.000','2005-02-28 16:20:00')

INSERT TB_ORDER VALUES (2,'NILTON',

  '2006-02-15 13:58:32.823','2006-02-16 00:00:00')

INSERT TB_ORDER VALUES (3,'WILLIAN',

  '2006-02-27 00:00:00.000','2006-02-27 16:00:00')

INSERT TB_ORDER VALUES (4,'WILLIAN',

  '2006-02-27 10:15:56.833','2006-02-28 00:00:00')

INSERT TB_ORDER VALUES (5,'MARY',

  '2006-08-10 00:00:00.000','2006-08-10 00:00:00')

INSERT TB_ORDER VALUES (6,'JOHN',

  '2006-08-10 10:21:15.637','2006-08-10 17:20:00')

 

A very common search, when we work with date and time, is searching for registers of a specific date independent of the time. Using the data of the orders’ table, an example would be to search for all the orders placed on the day 10-08-2006.

If the date in the column is being stored with the time part being 0 (as in the case of register 5), there will be no problems since, as we see, when you search for a date without informing the time part, the SQL Server considers the hour as being 0 or midnight. In such manner, it will search through all the registers that contain the date the 10-08-2006 and time part as 00:00: 00.

However, as we can see in the example data, as much the Data_Order column as well as the Data_Send are used in an inconsistent manner, that is, sometimes the time part is informed and sometimes not. This can indicate that the objective of the developer was to store only the date part, but this was not forced by the application and ended up generating registers with the time part different than 0.

The consequence of this is that if you fire a query wanting to know all the orders with sending date equal to 10-08-2006 (without informing the time part), the result obtained will not be the one expected.

The SELECT for the acquisition of these registers would be:

 

SELECT * FROM TB_ORDER

  WHERE SendDate = '2006-08-10'

 

When executing, the result only shows register 5 when the expected would be registers 5 and 6. This happens because as the time part was not informed in the research, the SQL Server will search for the date where the time part is 0. Since the time for register 6 is 17:20:00, the same is not returned.

Thus being, what we can do to bypass this problem? If this type of query is used a great deal by your application, the suggestion is that you start working with a range of values. Example:

 

SELECT * FROM TB_ORDER

  WHERE SendDate BETWEEN '2006-08-10'

  AND '2006-08-10 23:59:59.997'

 

Remember that the BETWEEN clause gets values that are between the first and the second informed values (also known as inferior and superior limits). You cannot define the superior limit as 2006-08-10, thus, once again, you will obtain only register 5, since the SQL Server will treat the hour as being 0.

Observe that in the example, the superior limit was defined as “2006-08-10 23:59:59.997”. With this, the SQL Server will search for all the registers between “2006-08-10 00:00:00.000” and “2006-08-10 23:59:59.997”, thus taking the whole range of the day’s schedule and then obtaining all the registers of the day 10-08-2006. See the returned data in Figure 4.

 

Figure 4. Data correctly returned

 

Another way to obtain the expected result is by using comparison operators >= (bigger equal) and < (smaller), as demonstrated in the code:

 

SELECT * FROM TB_ORDER

  WHERE OrderDate >= '2006-08-10' AND

  OrderDate < '2006-08-11'

 

In this case, the SQL Server will interpret the dates as “2006-08-10 00:00:00.000” and “2006-08-11 00:00:00.000”. Since the filter searches for registers with order date smaller than “2006-08-11 00:00:00.000”, then the SQL Server will bring only the registers of the day 10-08-2006 until the schedule of 23:59: 59.997.

 

Functions and conversions

A common practice of many developers is to make use of some internal functions of the SQL Server to simplify the work with dates. Some of the most common functions are listed in Table 3.

 

Function

Description

Example

DATEDIFF()

Calculates the difference between two dates. Syntax:

DATEDIFF (datepart, startdate, enddate )

-- Returns the day of the specified date.

SELECT DATEDIFF(day, ‘2006-10-17’, ‘2006-10-18’)

 

For datediff, you may use: Year, quarter, Month, dayofyear, Day, Week, Hour, minute, second and millisecond.

DATEPART()

Returns specific parts of a date.

Syntax:

DATEPART (datepart, date)

-- Returns the minute of the specified date.

SELECT DATEPART(minute, ‘2006-10-18 15:22:56.567’)

 

For datepart, you may use: Year, quarter, Month, dayofyear, Day, Week, Hour, minute, second and millisecond.

DATEADD()

Returns a new date based on the addition of an interval for a specified date.

Syntax: DATEADD (datepart, number, date)

-- Adds 3 days to the specified date.

SELECT DATEADD(day, 3, ‘2006-10-18 15:22:56.567’)

 

For datepart, you may use: Year, quarter, Month, dayofyear, Day, Week, Hour, minute, second and millisecond.

DAY()

Returns an integer representing the day part of a date. Syntax: DAY (date)

-- Returns the value 18

SELECT DAY(‘2006-10-18 15:22:56.567’)

MONTH()

Returns an integer representing the month part of a date. Syntax:

MONTH (date)

-- Returns the value 10

SELECT MONTH(‘2006-10-18 15:22:56.567’)

YEAR()

Returns an integer representing the year part of a date. Syntax: YEAR(date)

-- Returns the value 2006

SELECT YEAR(‘2006-10-18 15:22:56.567’)

CAST()

Converts a type of data into another. Syntax: CAST (expression AS data_type)

-- Converts the data string into the DateTime data type and then to Float, obtaining date as a Float value (3.5)

 

SELECT CAST(CAST(‘1900-01-04 12:00’ AS datetime) AS float)

CONVERT()

Similar to CAST. Syntax: CONVERT (data_type [(length)] , expression [style])

-- Converts the string into the DateTime data type and then into to a 10 positions VarChar, obtaining only the date part in the dd/mm/yyyy format (04/01/1900).

 

SELECT CONVERT(VarChar(10), CAST(‘1900-01-04 12:00’ AS datetime), 103)

 

There are various style options (optional), but the mostly used are:

103: Returns only the date part in the dd/mm/yyyy format,

108: Returns only the time part in the hh:mm:ss format,

114: Returns only time part in the hh:mi:ss:mmm(24h) format.

 

* To see other styles, consult the Books Online of the SQL Server.

Table 3. Functions of the SQL Server

 

In case your query is not used frequently, you can really make use of some of these functions to simplify the work with the dates. However, I must alert you that it is definitively not good a practice.

This because, although the use of functions allows you to obtain the results in a relatively simple manner, when you use a function in a search condition, you end up killing the table’s index, that is, the SQL Server end up not using the index to optimize the search.

Still using the orders’ table examples’ data (Figure 3), an option would be to use the CONVERT function to obtain all the registers with Data_Order equal to 10-08-2006.

 

SELECT * FROM TB_ORDER

  WHERE CONVERT(VarChar(10),

  OrderDate, 103) = '10/08/2006'

 

In this example, the function converts the Data_Order field into a 10 positions VarChar, thus taking only the date part in the dd/mm/yyyy format and comparing with value 10/08/2006. As a result, the SELECT will return registers 5 and 6, as desired.

Another example to obtain the same results would be to use MONTH and DAY and YEAR functions:

 

SELECT * FROM TB_ORDER

  WHERE DAY(OrderDate)=10 AND

  MONTH (OrderDate)=08 AND

  YEAR (OrderDate)=2006

 

Again, although the use of functions is a possible solution, its use must be extremely prevented. This is due to the fact that, as you must imagine, the function will be called for each register in the table, killing the table’s index and generating an elevated expenditure of the server’s resources.

For a table with millions of registers, this practice certainly will result in possible impacts in the performance. My suggestion is that you try to use the solution with the BETWEEN or the comparison operators >= (bigger equal than) and < (smaller than).

 

Working with the time part

Performing a query which searches for a specific time is similar as performing a query that searches only for a date (without the time). If the column stores only the part referring to the time, the search for the time will be simple.

However, different than the date values, the value referring to the time is represented by an approximate numerical value. We can easily see this when executing the following instruction, where the returned value will be 0.983321759259259:

 

SELECT CAST(CAST('23:35:59' AS DateTime) AS Float)

 

To illustrate the search only for the time, consider a table of time notes with the registers presented in Figure 5.

 


Figure 5.
Time notes table

 

The script for creation of the table and population of the example data is found in Listing 2.

 

Listing 2. Script for creation and population of the time notes table

CREATE TABLE TB_TIME (ID int,

  Employee VarChar(10),

  Date SmallDateTime,

  EntranceTime DateTime,

  ExitTime DateTime)

 

INSERT INTO TB_TIME VALUES (1, 'NILTON',

  '2006-10-10','08:30:00','12:30:15')

INSERT INTO TB_TIME VALUES (2, 'NILTON',

  '2006-10-10','13:32:00','17:38')

INSERT INTO TB_TIME VALUES (3, 'WILLIAN',

  '2006-10-11','2006-10-11 08:30','12:30')

INSERT INTO TB_TIME VALUES (4, 'WILLIAN',

  '2006-10-11','2006-10-11 13:30','17:30')

INSERT INTO TB_TIME VALUES (5, 'MARY',

  '2006-10-12','08:30:01.997','12:30')

INSERT INTO TB_TIME VALUES (6, 'JOHN',

  '2006-10-12','13:30','17:30')

 

Here, the EntranceTime column is used in an inconsistent manner, that is, sometimes only storing the time (the date part is defined as January, 1st, 1900), other times storing the date and the time (registers 3 and 4).

With this, if you to use the following query to get only the registers with entrance time equal to 08:30, you will have as a result only register 1:

 

SELECT * FROM TB_TIME WHERE EntranceTime = '08:30'

 

Register 3 is not returned because, when research is done only by the time, the SQL Server understands that the part referring to the date must be 0, which is equivalent to the base date. On the other hand, register 5 is not returned because even though the value is sufficiently approximate, the same is not 08:30.

With the execution of the following code, it is possible to see how the SQL Server understands these two schedules in numerical format:

 

SELECT CAST(CAST('08:30' AS DateTime) AS Float),

  CAST(CAST('08:30:01.997' AS DateTime) AS Float)

 

Notice that the time 08:30 is understood by the SQL Server as the value 0.354166666666667, whereas the time 08:30:01.997 is understood as 0.354189776234568. That is, they are really very different.

In these cases, to ignore the date part of a DateTime or SmallDateTime column, you can use the CONVERT function to separate to the time value from its date component:

 

SELECT * FROM TB_TIME

  WHERE CONVERT (VarChar(5),

  EntranceTime, 108) = '08:30'

 

This returns the registers 1, 3 and 5. If the time part is stored in an inconsistent manner, then you will also be able to consider the execution of searches by range of time values. The following instruction returns the registers 1, 3 and 5.

 

SELECT * FROM TB_TIME

  WHERE CONVERT (VarChar(5),

  EntranceTime, 108) >= '08:20' AND

  CONVERT (VarChar(5), EntranceTime, 108) <='08:40'

 

Unfortunately, there is not a way to obtain this result without using one or more functions. Again, for performance reasons, avoid the use of functions in fields used in search.

If the time part is stored in a consistent manner, that is, without the part referring to the date, as is the case of the ExitTime column, you will be able to avoid the use of functions, making use of queries such as the ones presented:

 

SELECT * FROM TB_TIME

  WHERE ExitTime BETWEEN '17:30' AND '17:40'

 

SELECT * FROM TB_TIME

  WHERE ExitTime >= '17:30' AND ExitTime <='17:40'

 

In these examples, both the consultations return registers 2, 4 and 6. Another way to work more easily with time values is to use the SmallDateTime data type instead of the DateTime.

Since the SmallDateTime always rounds off the time part to the closest minute (above or below), the hours between 08:59:29.999 and 09:00:29.998 will be stored as 09:00. If this type of rounding off is possible for your application, then the use of the SmallDateTime will prevent the necessity of searches by range of time values.

 

Conclusion

As we saw along the development of this article, the work with date in the SQL Server is relatively simple when we understand how the server stores and treats the values. One of the first points to be known when we work with date is if it will really require a precision of milliseconds.

With this you will already decide between the type of DateTime or SmallDateTime data. Remembers that the DateTime takes up more space (8 bytes) than the SmallDateTime (4 bytes) and when your objective is to treat only the time part, it may compel you to work with ranges of time values.

The second point is to always guarantee the consistency of the values, that is, when the essential is the date part, try to always keep the time part as 0; when the fundamental is the time part, try to always keep the date part as January, 1st, 1900. Certainly, these precautions will greatly simplify your work with date and time values.

Cheers and until the next time we meet.



colunista nao disponivel

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login