Introduction to Time Values

Overview

A time is a non-spatial measure used to count a certain number of lapses that have occurred from a non-spatial starting point. The primary starting point is called midnight. That is, it is positioned in the middle of the night.

The primary unit of measure of time is called the second. A second is identified by an integer. A second itself divided in 1000 fractions, counted from 0 to 999 and called milliseconds. A millisecond is identified by an integer. A measure of 60 consecutive seconds, counted from 0 to 59, is called a minute.  A minute is identified by an integer. A group of 60 minutes, counted from 0 to 59, is called an hour.  An hour is identified by an integer. A group of 24 consecutive hours is called a day.

The rules to represent a time value are specified in the Time tab of the Customize Regional Options of the Regional and Language Options of the Control Panel:

We continue with our University5 database. Before the beginning of a semester, the school must publish a catalog. The catalog has a list of schedules for each course. The students must consult that catalog to enroll in a course. The catalog specifies: Semester: This is the primary target of the schedule Course Code: This is a number or code for a course that is taught in the semester Weekdays: This indicates on what days a course is taught. Some courses are taught once a week. Some courses are taught twice a week, such as Monday and Wednesday, or Tuesday and Thursday. Some course are taught three times a week, such as Monday, Wednesday, and Friday Start Time: Courses are taught at different times of the day or evening. Some courses are taught online. Such courses don’t have start time End Time: If a course has a start time, it surely should also have an end time. Obviously if a course is taught online, it doesn’t have an end time In this lesson, we will create a table for schedules.

Practical Learning: Introducing Time Values

1. Start the computer and log in
2. Launch Microsoft SQL Server and click Connect
3. In the Object Explorer, right-click University5 and click New Query

Time Values

To support time values, Transact-SQL provides a data type named TIME.  You can use it to declare a time-based variable or you can specify that type for a column. To initialize a TIME-based variable, provide its value as a string that use the following formula:

hh:mm
hh:mm:ss
hh:mm:ss[.fractional seconds]

In Transact-SQL, a time-value is created as a string in single-quotes. To indicate that you want to follow Unicode rules, precede the value with N.

The first part represents the hour with a value between 1 and 23. Any value outside of this range will cause an error. If the value is less than 10, you can optionally write it with a leading 0, as in 08. The second part represents the minutes and holds a value between 1 and 59; otherwise, an error would be thrown. If the value is less than 10, you can type it with a leading 0, as in 04. The values are separated by :. An example is 10:25.

Here is an example of declaring and initializing a time-based variable:

1> DECLARE @ArrivalTime time;
2> SET @ArrivalTime = N'18:22';
3> SELECT @ArrivalTime AS [Arrival Time];
4> GO
Arrival Time
----------------
18:22:00.0000000

(1 rows affected)
1>

The third part of our formula is optional and represents the seconds portion of the time. It holds a value between 1 and 59. If the value is less than 10, you can provide it with a leading 0. This part is separated from the previous one with :. This means that you can also initialize a time value using the hh:mm:ss formula as a string. Here is an example:

DECLARE @TimeValue TIME;
SET @TimeValue = N'20:22:52';

SELECT @TimeValue;
GO

This would produce:

The last part also is optional. It allows you to provide the milliseconds part of the time. If you want to provide it, enter a value between 1 and 999. This is separated from the seconds part with a period ".". 

A time value can be represented in standard or military format. In standard format, you must indicate whether the time occurs before noon or after noon. In both cases, the hour part is given between 0 and 11. If the time occurs before noon, the value ends with AM. An example is 08:12 AM. If the time occurs after noon, the value ends with PM. An example is 04:35 PM.

In military time, the hour part is given between 00 and 23. It includes neither AM nor PM. Examples are 08:12 or 15:18. If the hour portion is between 12 and 23, the time is set in the afternoon. This means that you  can also initialize a time value by specifying the AM/PM portion as follows. Here is an example:

DECLARE @TimeValue TIME;
SET @TimeValue = N'10:22:52 PM';

SELECT @TimeValue;
GO

This would produce:

In the absence of an AM/PM section, the database interpreter would evaluate the hour portion to find out whether the time occurs in the morning or in the afternoon. The AM and the PM can be in uppercase or lowercase. In other words the AM/PM portion can be represented as AM, Am, aM, am, PM, Pm, pM, or pm. Only the characters A and P (uppercase or lowercase) are accepted as the first character. Only the M or m characters are accepted as the second character. Any other combination or other character will cause an error.

Time-Based Columns

To create a column that would store time values, select or apply TIME as the data type:

To specify the time value of a TIME-based column, you use the same formulas we saw for the variables:

hh:mm
hh:mm:ss
hh:mm:ss[.fractional seconds]

Practical Learning: Creating Time Values

1. In the Object Explorer, expand University5 if necessary.
   Right-click its Tables node and click New Table...
2. While the table is displaying, in the Properties window, click Schema, then click the arrow of its combo box and select Academics
3. Create the columns as follows:

   Column Name	Data Type
   CourseCode	nvarchar(10)
   Semester	nvarchar(20)
   StartTime	time(7)
   EndTime	time(7)
   Weekdays	nvarchar(20)

4. Close the table
5. When asked whether you want to save it, click Yes
6. Set the name to UndergraduateSchedules
7. Click OK
8. In the Object Explorer, right-click the Tables node of University5 and click Refresh
9. Right-click Academics.UndergraduateSchedules and click Edit Top 200 Rows
10. Create some records as follows (enter the values as provided in the following table and watch how they display):
     

    Course Code	Semester	StartTime	EndTime	Weekdays
    WRTG 101	SPRING 2012	08:15	10:10	M-W
    WRTG 245	SPRING 2012	10:25	12:20	M-W
    CHEM 101	SUMMER 2012	13:25	15:20	M-W
    WRTG 215	SUMMER 2012	13:25	15:20	M-W
    CMIS 210	SUMMER 2012	08:20	17:00	S

11. Close the table
12. Click inside the Query Editor and to create a few record, type the following:
    

    USE University5;
    GO
    INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays)
    VALUES(N'MATH 106', N'SPRING 2012', N'08:15', N'10:10', N'M-W');
    GO
    INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays)
    VALUES(N'MATH 120', N'SPRING 2012', N'08:15 AM', N'10:10 AM', N'M-W');
    GO
    INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays)
    VALUES(N'SOCY 100', N'SUMMER 2012', N'15:35', N'17:20', N'T-H');
    GO
    INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays)
    VALUES(N'GVPS 140', N'SUMMER 2012', N'3:35 PM', N'5:20 PM', N'T-H');
    GO

13. Press F5 to executePress F5 to execute

Data Analysis On Time Values

   

Introduction

When you sort records based on a column that uses a time-based column, the SQL interpreter must be able to identify each time value. Fortunately, the database engine will have validated each time value and reject those that were not valid.

If you sort records based on a column that uses a time type and if the column has null fields, the records with null would show up first. The values are sorted in their order of occurrence. If the table has only time values, the database engine would (or may) consider that all time values occur on the same day. In that case (or if that's the case), if you sort the records on the column that has the time values, the values closer to 0:01 AM would first appear, then the values that occur closer to 23:59 (midnight) on the same day would appear last.

Practical Learning: Sorting Time-Based Fields

1. Click inside the Query Editor and press Ctrl + A
2. To select and sort records, type the following:

   USE University5;
   GO
   SELECT Semester,
          CourseCode,
          StartTime,
          EndTime,
          Weekdays
   FROM Academics.UndergraduateSchedules
   ORDER BY StartTime;
   GO

3. To execute, press F5

 

Time Values and Logical Comparisons

Remember that Transact-SQL supports all regular logical operators:

• When the equality comparison is performed on time values, they must occur at the exact same time, not a second earlier or later. This means that the data type specified for the column and the way data entry was performed are very important. For example, if the user enters the seconds on one time record and not on another, comparison for equality can become unpredictable. For example, 14:22 and 14:22:01 are different values.
• If the inequality comparison is performed on time values, it may produce more positive (meaning that the values are different) than negative

• If the comparison for lower value is performed on two time values, the first time value must occur before the second

Practical Learning: Filtering Time-Based Fields

1. Click inside the Query Editor and press Ctrl + A
2. To see the courses that strt in early afternoon, type the following:

   SELECT Semester,
          CourseCode,
          StartTime,
          EndTime,
          Weekdays
   FROM Academics.UndergraduateSchedules
   WHERE StartTime = N'01:25 PM';
   GO

3. To execute, press F5

Time Values and Functions

 

Creating a Time From Parts

As mentioned already, a time value is made of an hour value, a minute value, and the seconds. We already saw how to supply those values as a string. As an alternative, to let you create a time value if you have those parts, Transact-SQL provides a function named TIMEFROMPARTS. Its syntax is:

TIMEFROMPARTS(int hour, int minute, int seconds, int fractions, int precision) RETURNS time;

The hour value must be between 0 and 23. The minute must be between 0 and 59. The second argument must have a value between 0 and 59. Whenever the seconds are not important to represent the time, provide their value as 0. Here is an example:

DECLARE @TimeValue time;
SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 0, 0);
SELECT @TimeValue;
GO

This would produce:

A time value is subject to precision, that is, how accuracte the value should be. That's the role of the last two arguments of the TIMEFROMPARTS() function. These two arguments should be provided together because one controls the other:

• If precision is passed as 0 or 1, fraction must be 0 and represents 5/100 of seconds
• If precision is passed as 2, fraction must be between 0 and 99 and represents 50/100 of seconds. Here is an example:

  DECLARE @TimeValue time;
  SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 99, 2);
  SELECT @TimeValue [Time Value];
  GO

  This would produce:

  

• If precision is passed as 3, fraction must be between 0 and 999 and represents 500/1000 of seconds. Here is an example:

  DECLARE @TimeValue time;
  SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 999, 2);
  SELECT @TimeValue [Time Value];
  GO

  This would produce:

  

• If precision is passed as 4, fraction must be between 0 and 9999 and represents picoseconds
• If precision is passed as 5, fraction must be between 0 and 99999 and represents the nanoseconds
• If precision is passed as 6, fraction must be between 0 and 999999 and represents 10 nanoseconds
• If precision is passed as 7, fraction must be between 0 and 9999999 and represents 100 nanoseconds Here is an example:

  DECLARE @TimeValue time;
  SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 9999999, 7);
  SELECT @TimeValue [Time Value];
  GO

  This would produce:

  

The fraction argument must not have a value other than those ones; otherwise you would receive an error.

Practical Learning: Creating Time Values

1. Click inside the Query Editor and change the statement as follows:

   INSERT INTO Academics.UndergraduateSchedules(Semester, CourseCode,
               StartTime, EndTime,Weekdays)
   VALUES(N'FALL 2012', N'CMIS 210', TIMEFROMPARTS(8, 20, 0, 0, 0),
          TIMEFROMPARTS(17, 0, 0, 0, 0), N'M-T-W-H-F');;
   GO
   SELECT Semester,
          CourseCode,
          StartTime,
          EndTime,
          Weekdays
   FROM Academics.UndergraduateSchedules;
   GO

2. Press F5 to executePress F5 to execute

   

The Current Time

To let you get the current (date and) time of the computer, Transact-SQL provides various functions. One of them is named GETDATE. Its syntax is:

GETDATE();

This function simply returns the (date and) time of the computer where the function is called. As mentioned already, time is subject to precision. For that matter, Transact-SQL provides a function named SYSDATETIME. Its syntax is:

SYSDATETIME();

Converting a String to Time

As mentioned previously, to let you convert a value from one type to another, such as to convert a a string to a time, Transact-SQL provides the TRY_PARSE() and TRY_CONVERT() functions. These functions take a string as argument and they scan that argument. If the value is not a valid time, the functions returns NULL. Here is an example:

DECLARE @StrValue nvarchar(20),
        @TimeValue time;
SET @StrValue = N'1008';
SET @TimeValue = TRY_CONVERT(time, @StrValue);
SELECT @StrValue;
SELECT @TimeValue;
GO

This would produce:

If the value is a valid (date or) time, the function returns it. Here is an example:

DECLARE @StrValue nvarchar(20),
        @TimeValue time;
SET @StrValue = N'10:08';
SET @TimeValue = TRY_CONVERT(time, @StrValue);
SELECT @StrValue;
SELECT @TimeValue;
GO

This would produce:

Formatting a Time Value

Even though there is usually a standard way to display time, Transact-SQL provides a function to control how a time value should be presented. The function to do this is named FORMAT and its syntax is:

FORMAT(value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar

This function takes two required arguments. The third argument is optional. The first argument is the original time value. The second argument specifies how the formatting must be carried. The computer has a default way to display time. In US English, the default time is from 00:00 to 23:59. To present a time value in the default format, pass the second value as c (in lowercase). Here is an example:

DECLARE @StrValue nvarchar(20),
        @TimeValue time;
SET @TimeValue = N'09:24 PM';
SET @StrValue = FORMAT(@TimeValue, N'c');
SELECT @StrValue;
SELECT @TimeValue;
GO

This would produce:

Time Addition

One of the primary operations you may want to perform on a time value would consist of adding a number to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:

DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)

The third argument to this function is the value of a time on which the operation will be performed. It can be a constant value that uses a valid format. The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.

When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:

Here is an example that adds two hours to a time value:

DECLARE @TimeValue time,
	@Result time;
SET     @TimeValue = N'23:38';
SET     @Result = DATEADD(hh, 2, @TimeValue);
SELECT @TimeValue;
SELECT @Result;
GO

This would produce:

Time Subtraction

Another regular operation performed on a time value consists of getting the number of units that has elapsed in the range of two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:

DATEDIFF(TypeOfValue, StartDate, EndDate)

This function takes three arguments. The second argument is the starting time of the range to be considered. The third argument is the end or last time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function. Here is an example:

DECLARE @Start time,
	@End time,
	@Result int;
SET     @Start = N'12:24';
SET     @End = N'15:06';
SET     @Result = DATEDIFF(n, @Start, @End);
SELECT @Start [Start Time];
SELECT @End [End Time];
SELECT @Result;
GO

This would produce:

The Part Name of a Time Value

Sometimes you want to get a component of a time value, such as the hour, the minute, or the second. To assist you with this, Transact-SQL is equipped with a function named DATENAME. Its syntax is:

DATENAME(integer ReturnedValue, date Value) RETURNS nvarchar;

The first argument specifies the value you want to get from the function. That argument can be one of the following:

The second argument is the time that holds the value from which the time will be produced. Here is an example:

DECLARE @TimeValue time,
	@Result int;
SET     @TimeValue = N'23:38';
SET     @Result = DATENAME(hh, @TimeValue);
SELECT @TimeValue;
SELECT @Result;
GO

Besides DATENAME(), Transact-SQL also provides a function named DATEPART. Its syntax is:

int DATEPART (integer datepart, date Value)

This function takes the exact same arguments as DATENAME and both functions essentially behave the same. The main difference is that DATENAME() returns a string while DATEPART() returns an integer. Remember that what you are interested in is the value of the first argument. This means that these two functions perform the same operation.

Using Date and Time Values

Introduction

Instead of using only the time or only a date value, you may want to use theic combinaion. You have two options. You can use two separate variables or columns, or you can combine both values in one unit.

We already know how to declare a date variable, how to create a date-based column, how to declare a time variable, and how to create a time-based column. In the same way, you can create a table that uses one or many combinations of date-based columns and time-based columns. After creating a column, you can add the records as necessary.

Data analysis on date-based columns and time-based columns follows the descriptions with which we are already familiar.

If you sort records on a table that uses combinations of date and time columns:

• If you sort the records on the date-based columns only, they would be sorted in chronological order regardless of the chronology of the time-based columns
• If you sort the records on the time-based columns only, they would be sorted in chronological order regardless of the chronology of the date-based columns
• If you sort the records using a combination of a date-based column followed by a time-based column, the records would first be grouped by the dates that occur together. Then, inside of the group, the records would be sorted based on the chronology of time
• If you sort the records using a combination of a time-based column followed by a date-based column, the records would first be grouped by the times that occur together. Then, inside of the group, the records would be sorted based on the chronology of date

 

Practical Learning: Using a Combination of Date/Time Columns

1. In the Object Explorer, right-click Academics.UndergraduateSchedules and click Delete
2. Click inside the Query Editor and press Ctrl + A
3. To create a table, type the following:

   CREATE TABLE Academics.UndergraduateSchedules
   (
       Semester 	  nvarchar(20),
       StartDate  	  date,
       EndDate       date,
       StartTime  	  time,
       EndTime 	  time,
       Weekdays 	  nvarchar(32),
       CourseCode 	  nvarchar(10)
   );
   GO
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'08:15', N'10:10', N'M-W', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'M-W', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'08:15', N'10:10', N'M-W-F', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'MATH 106');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'08:15', N'10:10', N'T-H', N'MATH 106');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'13:25', N'15:30', N'T-H', N'EDPD 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'T-H', N'EDPD 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'13:25', N'15:30', N'M-W', N'EDPD 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'M-W', N'EDPD 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'WRTG 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'M-W', N'EDPD 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'MATH 106');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'13:25', N'15:20', N'M-W', N'MATH 115');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'HIST 140');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'T-H', N'HIST 140');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'08:15', N'10:10', N'M-W', N'SOCY 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'10:25', N'12:20', N'M-W', N'SOCY 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'08:15', N'10:10', N'T-H', N'SOCY 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'10:25', N'12:20', N'T-H', N'SOCY 100');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'15:35', N'17:30', N'T-H', N'HIST 215');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'13:25', N'15:20', N'T-H', N'HIST 215');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'FINA 101');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'M-W-F', N'GVPS 140');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'T-H', N'HIST 216');
   INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'ACCT 311');
   GO

4. Press F5 to execute
5. Click inside the Query Editor and press Ctrl + A
6. To sort the records based on a date field, type the following code:
   

   SELECT Semester,
          StartDate "Start Date", 
          EndDate "End Date", 
          StartTime "Start Time", 
          EndTime "End Time", 
          Weekdays, 
          CourseCode "Course Code"
   FROM Academics.UndergraduateSchedules
   ORDER BY StartDate;
   GO

7. To execute the statement, press F5
8. To sort the records based on a date field, change the statement as follows:

   SELECT Semester,
          StartDate "Start Date", 
          EndDate "End Date", 
          StartTime "Start Time", 
          EndTime "End Time", 
          Weekdays, 
          CourseCode "Course Code"
   FROM Academics.UndergraduateSchedules
   ORDER BY StartTime;
   GO

9. To execute the statement, press F5
10. To sort records based on a date-based field followed by a time-based field, change the statement as follows:

    SELECT Semester,
           StartDate "Start Date", 
           EndDate "End Date", 
           StartTime "Start Time", 
           EndTime "End Time", 
           Weekdays, 
           CourseCode "Course Code"
    FROM Academics.UndergraduateSchedules
    ORDER BY StartDate, StartTime;
    GO

11. To execute the statement, press F5
12. To sort records based on a time-based field followed by a date-based field, change the statement as follows:

    SELECT Semester,
           StartTime "Start Time",
           EndTime "End Time", 
           StartDate "Start Date",
           EndDate "End Date", 
           Weekdays, 
           CourseCode "Course Code"
    FROM Academics.UndergraduateSchedules
    ORDER BY StartDate, StartTime;
    GO

13. To execute the statement, press F5

Combining Date and Time

So far, we have used date values and time values in separate variables and columns. Transact-SQL gives you the ability to combine a date and time values into one unit. To support this, it provides the DATETIME2 data type. This data type counts dates from January 1st, 0001 and ends on December 31st, 9999. Probably the biggest feature of this data type is that it is a combination of the date and the time data types. This means that everything we have seen about both types is available here.

To declare a variable that can combine date and time values, use the DATETIME2 data type. In the same way, you can create a column of a table and apply this data type to it (the column). To initialize the variable or assign a value to the column, you can use any of the formulas we saw for dates or for times, or combine those formulas. This means that you can use one of the following formulas:

YYYYMMDD
YYYYMMDD hh:mm:ss
YYYYMMDD hh:mm:ss[.fractional seconds]
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
YYYY-MM-DD hh:mm:ss[.fractional seconds]
MM-DD-YY
MM-DD-YY hh:mm:ss
MM-DD-YY hh:mm:ss[.fractional seconds]
MM-DD-YYYY
MM-DD-YYYY hh:mm:ss
MM-DD-YYYY hh:mm:ss[.fractional seconds]
MM/DD/YY
MM/DD/YY hh:mm:ss
MM/DD/YY hh:mm:ss[.fractional seconds]
MM/DD/YYYY
MM/DD/YYYY hh:mm:ss
MM/DD/YYYY hh:mm:ss[.fractional seconds]

Remember to include the value in single-quotes. Other than that, all the ways we saw to specify the value of a date or of a time can be used with the DATETIME2 data type. As mentioned already, you can also combine the values. Here are examples:

DECLARE @FullName nvarchar(60),
     	@DateOfBirth date,
	@DateRegistered datetime2

SET @FullName       = N'John Summons';
SET @DateOfBirth    = N'19960426';
SET @DateRegistered = N'20090629';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'James Haans';
SET @DateOfBirth    = N'1994-10-25';
SET @DateRegistered = N'2009-08-02';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Gertrude Monay';
SET @DateOfBirth    = N'06-16-92';
SET @DateRegistered = N'2009-12-24 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Philomène Guillon';
SET @DateOfBirth    = N'1996-10-16';
SET @DateRegistered = N'10/14/08 09:42:05.136';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Eddie Monsoon';
SET @DateOfBirth    = N'08/10/96';
SET @DateRegistered = N'2009-06-02 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Peter Mukoko';
SET @DateOfBirth    = N'03-10-1994';
SET @DateRegistered = N'7/22/2009 10:24:46.248';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Chritian Allen';
SET @DateOfBirth    = N'06/16/1995';
SET @DateRegistered = N'02-09-2009 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];
GO

Besides the DATE, the TIME, and the DATETIME2 data types, Transact-SQL supports the smalldatetime and the datetime data types. These are old data types. Although still available, they are kept for backward compatibility and you should avoid using them.

A Date/Time From Parts

One of the ways you can create a value that is a combination of date and time is by using a function named DATETIME2FROMPARTS. Its syntax is:

DATETIME2FROMPARTS(int year, 
                                  int month, 
                                  int day, 
                                  int hour, 
                                  int minute, 
                                  int seconds, 
                                  int fractions, 
                                  int precision) RETURNS datetime2;

As you can see, this function takes 8 required arguments. As mentioned already, the datetime2 is a combination of date and time. Based on this, the first 3 arguments are the same used in the DATEFROMPARTS() function. The other arguments are the same used in the TIMEFROMPARTS() Function.

Data Analysis On Date/Time Values

   

Sorting Date and Time-Based Fields

If you sort records based on a column that uses a combination of a date and a time values, if the column has null fields, the records with null would display first. If the values are combinations of date and time values, the date values would first be sorted in chronological order. If some records occur on the same day but at different times, the records with similar dates would be grouped first. Then inside a group, the records woulds be sorted by time. Consider the following example:

USE master;
GO
CREATE DATABASE IceCreamFactory1;
GO
USE IceCreamFactory1;
GO
CREATE SCHEMA IceCream;
GO
CREATE TABLE IceCream.Orders
(
	OrderID int identity(1, 1) not null,
	OrderedPeriod DateTime2 null
);
GO
INSERT IceCream.Orders(OrderedPeriod)
VALUES(N'2012-02-14 10:12'), (N'2012-02-15 09:08'),
      (N'2012-05-10 15:24'), (N'2012-07-04 14:01'),
      (N'2012-04-18 19:16'), (N'2012-04-18 09:15'),
      (N'2012-04-18 12:48'), (N'2012-07-04 11:26');
GO
SELECT OrderID, OrderedPeriod FROM IceCream.Orders
ORDER BY OrderedPeriod;
GO

This would produce

Logical Comparisons on Date/Time Fields

You can perform all types of logical comparisons on variables or columns that use the datetime2 data type. The value may have a date only,  a time only, or a combination of date and time. If the value has only a date, the comparison follows the description we saw for the date data type. If the value has only a time, the comparison follows the rules for the time data type. If the value uses a combination of date and time:

• If you compare the values for equality, =, both must occur on the same day and the same time to produce a true value. Here is an example:

  USE master;
  GO
  DECLARE @FirstOrder datetime2,
  	    @SecondOrder datetime2;
  SET @FirstOrder =  N'2012-02-14 10:12:00';
  SET @SecondOrder = N'2012-02-14 10:12:00';
  
  IF @FirstOrder = @SecondOrder
  	PRINT N'Both orders were made at the same time.';
  ELSE
  	PRINT N'The orders were made at different times.';
  GO

  This would produce

  

  If there is a slight difference in the value, even for one second, the equality comparison produces a false value:

  

• If you perform a comparison for different values, <>, any difference in the values would produce a true result
• To find out if one period occurs BEFORE the other, you compare using the Less Than < operator. If the first value occurs before the second, the comparison produces true. If the date parts are equal, the first time period must occur before the second. Here is an example:

  USE master;
  GO
  DECLARE @FirstOrder datetime2,
  	    @SecondOrder datetime2;
  SET @FirstOrder =  N'2012-02-14 10:12:00';
  SET @SecondOrder = N'2012-02-14 10:12:01';
  
  IF @FirstOrder < @SecondOrder
  	PRINT N'The first order was placed before the second.';
  ELSE
  	PRINT N'Either both orders were made at the same time or the first was after the second.';
  GO
  
  

• If you want to know whether both values occurred at the same time OR the first occurred BEFORE the second, use the Less Than Or Equal operator <=. An alternative is to use the Not Greater Than operator, !>, to perform the same comparison
• To find out if one value occurred after the other, perform the comparison using >. In this case, if the first value is chronologically higher than the second, the comparison produces true
• If you want to know whether both values occurred at the same time OR the first occurred AFTER the second, use the Greater Than Or Equal operator >=. You can also use the Not Less Than operator, !<, to perform the same comparison

Practical Learning: Ending the Lesson

1. Close Microsoft SQL Server
2. If asked whether you want to save the file, click No

Previous	Copyright © 2008-2022, FunctionX, Inc.	Next