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

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.

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]

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

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.

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

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

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

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.

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

   

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();

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:

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:

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:

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:

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.