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.

The primary unit of measure of time is called the second. A second is identified by an integer. In reality, the second is divided in 1000 fractions, counted from 0 to 999 and called milliseconds. A millisecond is identified by an integer.

Starting with the second, a measure of 60 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.

To represent a time value, there are rules you must follow. The rules can be verified in the Time tab of the Customize Regional Options of the Regional and Language Options of the Control Panel:

To support time values, Transact-SQL provides the TIME data type. To declare a variable that would hold a time value, use TIME as the data type. To initialize the variable, use the following formula:

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

The first part includes the hour with a value between 1 and 23. If the value is less than 10, you can write it with a leading 0, as in 08.

The second part represents the minutes and holds a value between 1 and 59. If the value is less than 10, you can type it with a leading 0, as in 04. The values are separated by :. The value is included in single-quotes. To indicate that you want to follow Unicode rules, precede the value with N. Here is an example:

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 and 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 :.

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 group of 24 hours, counted from 1 to 23, is called a day. In reality, a day is made of 24 hours and a few more seconds. Those are various ways used to identify a day. We will mention them below.

Above the day, the unit of measure is called a year. A year is identified by a numeric value. Normally, a year is represented with 4 digits, from 0 to 9999. To make it easy to manage years, they are grouped in some units. The years that occurred before Jesus Christ are identified as BC. Then, there is a starting point referred to as 0.

A group of 1000 years is called a millennium. The years in a millennium must each be identified with 4 digits. An example is 1608. Another example is 1978. Yet another example is 2118.

A group of 100 years is called a century. The years in a century can be identified with 2 digits. An example is 08. Another example is 78. One more example is 18 (do you predict some confusion here?). 

Within a year, each day can be identified by a numeric value. The first day is 1, or can be referred to as Day 1. Each of the other days in a year can be identified with a natural number, such as 216; that would be Day 216 starting from the beginning of the year.

The number of days in a year depends on various factors. For example, in some scenarios, such as some commercial or accounting procedures, a year would count for 360 days. In most calendars, a year can have 365 days every year except that, after 4 years, the year would have 366 days (remember, we mentioned that a day is actually made of 24 hours and a few seconds; these seconds are grouped every 4 years to count as a whole day). This is referred to as a leap year.

To help manage the days of a year, a year is divided in 12 units each called a month. Each month can be identified by a number or a name. When a month is identified with a number, it can use a value between 1 and 12.

When it comes to names, a month can use a long and/or a short name. The long names are January, February, March, April, May, June, July, August, September, October, November, and December. The short names are Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec.

Each month has a certain number of days. A day in a month can be identified with an integer. The first day of the month is 1. The 15th day would be identified as 15 or Day 15. The number of days in a month depends on various factors.

We mentioned that a day in a month can be identified with a number and a month has a name, within a year. A day can be identified by its number, its month, and the numeric value of the year. There are various rules you must follow to represent a date. The rules can be checked in the Date tab of the Customize Regional Options accessible from the Regional and Language Options of the Control Panel:

To help manage the months of a year, a year can be divided in either quarters or semesters. A year has 4 quarters that each contains 3 months. A year also has 2 semesters that each has 6 months.

To help manage the days in a month, the month in divided in parts each called a week. Normally, each week has 7 days and each month should have 4 weeks.

To assist you with date values, Microsoft SQL Server provides the DATE data type. This data type counts dates starting from January 1st, 0001 up to December 31st, 9999. Therefore, to declare a variable that would hold a date value, use the DATE data type.

To initialize a DATE variable, use one of the following formulas:

YYYYMMDD
YYYY-MM-DD
MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY

You can start the value with a 4-year digit. If you use the first formula, YYYYMMDD, you must provide 4 digits for the year, immediately followed by 2 digits for the month, immediately followed by 2 digits for the day. An example would be

DECLARE @OneDay DATE;
SET @OneDay = N'10360610';
SELECT @OneDay AS [Day to Prevail];
GO

In US English, this represents October 6th, 1036:

You can provide the value in one unit with 6 digits. In this case, the left 2 digits would be considered the year in the current century. Consider the following example:

Instead of providing the whole value in one combination of digits, you can use the second formula, YYYY-MM-DD, which is the default date format in Microsoft SQL Server. Once again you must provide 4 digits for the year, followed by the "-" separator, followed by 1 or 2 digits for the month, followed by the "-" separator, followed by 1 or 2 digits for the day. An example would be

DECLARE @EventDay date;
SET @EventDay = N'1914-4-7';
SELECT @EventDay AS [Event Day];
GO

In US English, this represents October 6th, 1036

If you are using a command prompt or PowerShell, make sure you include the value in single-quotes. To apply Unicode rules, start with the N prefix. Here is an example:

1> DECLARE @IndependenceDay DATETIME;
2> SET @IndependenceDay = N'01/01/1960';
3> SELECT @IndependenceDay AS [Independence Day];
4> GO
Independence Day
-----------------------
1960-01-01 00:00:00.000

(1 rows affected)

We saw that, if you use the MM-DD-YY or MM/DD/YY, you can provide a year with 2 digits. In this case:

1. If the number representing the year is less than 50, the year would be considered as belonging to the current century
2. If the number representing the year is greater than 50, the year is considered as belonging to the previous century

Here are examples:

DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-05';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/05';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-41';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/41';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-81';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/81';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO

Here are examples of results

Once again, it is better to provide a year with 4 digits.

1. Change the statement as follows:
   

   DECLARE @FirstName    nvarchar(20),
           @LastName     nvarchar(20),
           @FullName     nvarchar(40),
           @DateHired    date,
           @EmplStatus   int,
           @IsMarried    bit,
           @WeeklyHours  decimal(6,2),
           @HourlySalary SmallMoney,
           @WeeklySalary SmallMoney;
   SET @FirstName    = N'Samuel';
   SET @LastName     = N'Weinberg';
   SET @FullName     = @LastName + N', ' + @FirstName;
   SET @DateHired    = N'12/05/1998';
   SET @IsMarried    = 1;
   SET @EmplStatus   = 2;
   SET @WeeklyHours  = 36.50;
   SET @HourlySalary = 15.72;
   SET @WeeklySalary = @WeeklyHours * @HourlySalary;
   SELECT @FullName As [Full Name],
          @DateHired AS [Date Hired],
          @EmplStatus AS [Empl Status],
          @IsMarried AS [Married?],
          @WeeklyHours AS Hours,
          @HourlySalary AS Hourly,
          @WeeklySalary AS Weekly;
   GO

2. Execute the statement
    
   

Instead of singly declaring a date or a time value, you may want to combine both values into one. To support this, Transact-SQL provides the DATETIME2 data type. This data type counts dates from January 1st, 0001 and ends on December 31st, 9999. Therefore, to declare a variable that supports a date value, a time value, or a combination of a date and time values, use the DATETIME2 data type. To initialize the variable, 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. 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

If you start the value with two digits, the first part is considered a month and not the year.

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 stop using them.

Transact-SQL (and many other languages) provides data types that be used to hold almost any type of value, including values of types we have already reviewed. Because these types can be vague and difficult to identify, you should hardly use them.

Transact-SQL provides the sql_variant data type. It can be used in place of any of the data types we have seen so far. This means that you can declare its variable like any other. When initializing the variable, you should use the appropriate format, whether it is a string, a number, or a date, etc. Here are examples:

DECLARE @FullName SQL_VARIANT,
	@DateHired Sql_Variant,
        @IsMarried SQL_variant,
        @YearlyIncome sql_variant;

SET @FullName = N'Paul Yamo';
SET @DateHired = N'20110407';
SET @IsMarried = 1;
SET @YearlyIncome = 48500.15;

SELECT @FullName AS [Full Name];
SELECT @DateHired AS [Date Hired];
SELECT @IsMarried AS [Is Married?];
SELECT @YearlyIncome AS [Yearly Income];
GO

All of the data types we have used so far provided their values in a single and simple format. In fact, those values were based on types we have studied since elementary school. Transact-SQL provides some values that are based on coordinates of a geometrical shape or on space. These are referred to as spatial types.

Transact-SQL provides support for coordinates of a geometric figures. It does this through a data type named geometry. Based on this, you can declare a variable of this type. Here is an example:

DECLARE @Location geometry;

Unlike the data types we have used so far, the geometry data is in fact a type of object (in an object-oriented language, we would call it a class). This means that it has characteristics (called properties) and actions (called member functions or methods; a method is function created as a member of a class). The characteristics of the geometry type are defined by the Open Geospatial Consortium (OGC). To adapt the data type to Transact-SQL, Microsoft added some functionalities to the type.

As is usual for all data types, after declaring a geometry variable, you must initialize it before using it. As mentioned already, geometry is in fact a class and it has methods that allow it to perform actions. The most fundamental action is to initialize the variable. To support this, the class is equipped with a method named STGeomFromText. Its syntax is:

static geometry STGeomFromText('geography_tagged_text', SRID)

The static keyword indicates that this is a static method. This means that, to access it, you use geometry::STGeomFromText. This method returns a value or type geometry.  Here is an example:

DECLARE @Location geometry;

SET @Location = geometry::STGeomFromText(. . .)

This method takes two arguments. The first argument is holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.

If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:

DECLARE @Location geometry;

SET @Location = geometry::STGeomFromText('point(6 4)', . . .

Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:

DECLARE @Location geometry;

SET @Location = geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);

You can also use a complex geometric, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:

DECLARE @Location geometry;

SET @Location = geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );

The second argument of the  geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID).

After declaring and initializing the value, you can use a SELECT statement to display its value. Here is an example:

DECLARE @Location geometry;

SET @Location = geometry::STGeomFromText('point(6 4)', 0);

SELECT @Location;

Transact-SQL supports geographical locations.

If you have programmed in languages like C/C++ or Pascal, you are probably familiar with the ability to give a friendly name to a known data type. Transact-SQL also gives you this option. A user-defined data type (UDT) is a technique of creating a data type based on an existing Transact-SQL data type.

Before creating a user-defined data type, you must be familiar with the existing types. Those are the types we have seen so far. If you want, you can create an alias name for one of these. You can do this visually or programmatically.

To visually create a UDT, in the Object Explorer, expand a database, expand its Programmability node, and expand the Types item. Under Types, right-click User-Defined Data Types and click New User-Defined Data Type...

This would open:

The first piece of information you must provide is the schema that will own the new type. Normally, a default schema is provided and you can just accept it. The two most important pieces of information you must provide are a name for the new type as alias and the Transact-SQL type on which it will be based. In the Name text box, enter a name of your choice. The name must follow the rules of names in Transact-SQL. In the Data Type combo box, select the data type of your choice. Of course, you must know what type you want to use.

After entering and selecting the desired information, click OK.

To create a UDT with code, the basic formula to use is:

CREATE TYPE AliasName FROM BaseType

To get  assistance from template code, open a Query window. From the Templates Explorer, expand the User-Defined Data Type node. Drag Create User-Defined Data Type and drop it in the Query window. Skeleton code will be generated for you:

-- ================================
-- Create User-defined Data Type
-- ================================
USE <database_name,sysname,AdventureWorks>
GO

-- Create the data type
CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,Phone> 
	FROM <base_type,,nvarchar> (<precision,int,25>) <allow_null,,NULL>

-- Create table using the data type
CREATE TABLE <table_name,sysname,test_data_type>
(
	ID int NOT NULL,
	Phone <schema_name,sysname,dbo>.<type_name,sysname,Phone> NULL
)
GO

You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:

CREATE TYPE NaturalNumber FROM int;
GO

In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:

CREATE TYPE NaturalNumber FROM int;
GO
CREATE TYPE ShortString FROM nvarchar(20);
GO
CREATE TYPE ItemCode FROM nchar(10);
GO
CREATE TYPE LongString FROM nvarchar(80);
GO
CREATE TYPE Salary FROM decimal(8, 2);
GO
CREATE TYPE Boolean FROM bit;
GO

After creating a UDT, you can use it as you see fit. For example, you can declare a variable for it. Then, before using it, you must initialize it with the appropriate value. Here are examples:

DECLARE @EmployeeID NaturalNumber,
	@EmployeeNumber ItemCode,
        @FirstName ShortString,
        @LastName ShortString,
        @Address LongString,
        @HourlySalary Salary,
        @IsMarried Boolean;
SET     @EmployeeID = 1;
SET	@EmployeeNumber = N'28-380';
SET     @FirstName = N'Gertrude';
SET     @LastName = N'Monay';
SET     @Address = N'1044 Alicot Drive';
SET     @HourlySalary = 26.75;
SET     @IsMarried = 1;
SELECT  @EmployeeID AS [Empl ID],  @EmployeeNumber AS [Empl #],
        @FirstName AS [First Name], @LastName AS [Last Name],
        @Address, @HourlySalary AS [Hourly Salary],
        @IsMarried AS [Is Married ?];
GO

Of course, you can mix Transact-SQL data types and your own defined type in your code.

A composite operation consists of performing the operation from a variable to itself. For example, suppose you have a variable a that has a value and you want to change the value of that variable by adding its own value to itself. Composite operations use an operator that is in fact a combination of two operators. The variable can be almost any type that supports the type of operation you want to perform.

The composite operation uses the += operator. Using it, to add the value of a variable to itself, type the variable and insert this operation between both operands. Here is an example:

DECLARE @Variable int;

SET @Variable = 248;

SET @Variable += @Variable;

Once you have performed the operation, the variable holds the new value. Consider this:

As mentioned already, a variable that is involved in a composite operation can be of any type as long as the type supports that operation. For example, strings in Transact-SQL support the addition. This means that the variable can be of type char or any of its variants.

One variant of the composite operation is to add oone variable to another. To do this, include the += operator between the operants. Here is an example:

DECLARE @Name nvarchar(50);
DECLARE @LastName nvarchar(20);

SET @Name = N'Paul';
SET @LastName = N' Yamaguchi';

SET @Name += @LastName;

When the operation has been performed, the left operand now holds its value and that of the other variable:

Another variant of the composite operation consists of adding a constant to a variable. In this case, on the right side of the += operator, use the constant. Here is an example:

DECLARE @FullName nvarchar(50);

SET @FullName = N'Paul';

SET @FullName += N' Motto';
SELECT @FullName;

Once again, remember that when the operation has been performed, the variable holds the new value. Here is an example:

In the same way, you can perform this operation as many time as you want by adding right operands to a left operands. Here are examples:

DECLARE @Name nvarchar(50);
DECLARE @MiddleName nvarchar(20);
DECLARE @LastName nvarchar(20);

SET @Name = N'Paul';
SET @MiddleName = N' Bertrand';
SET @LastName = N' Yamaguchi';

SET @Name += @MiddleName;
SELECT @Name;

SET @Name += @LastName;
SELECT @Name;

One important thing you must keep in mind is the storage capacity of the left operand: It must be able to hold all values added to it.

The concept of composite operation can be applied to all arithmetic binary operations. As seen above, strings also support the addition composite operation. Composite operations are also available on all bit manipulation operations. The most important thing to remember is that not all data types support all operations. Overall:

• All arithmetic binary operators support the addition, the subtraction, the multiplication, the division, and the remainder binary operators. These are +=, -=, *=, /=, and &= respectively
• Bit manipulation operations support:
  • The composition bitwise AND operator &= that compares the bits of the left operand to those of the right operand, for equality:

    DECLARE @Number int;
    
    SET @Number = 215;
    SET @Number &= 15;
    SELECT @Number;

  • The composite bitwise OR operator |= that compares the bits of the left operand to those of the right operand, for inequality:

    DECLARE @Number int;
    
    SET @Number = 215;
    SET @Number |= 15;
    SELECT @Number;

  • The composite bitwise-exclusive OR operator ^=

    DECLARE @Number int;
    
    SET @Number = 215;
    SET @Number ^= 15;
    SELECT @Number;

You should know that these operations can be performed on natural or decimal numbers.

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

Lesson Review Questions