Foundations of Date Values

Introduction

A date is a non-spatial value used as a combination of numbers, symbols, and strings (or names). As such, a date is made of various parts. The primary part of a date is called a day. A day starts on a set point called midnight, which is in the middle of the night. The day ends at the other middle of the night, the other midnight. Such a period (from one midnight to the other midnight) is used as a unit. It can be represented with a number or it can be given a name. A series of 7 consecutive days is called a week. The days in a week can each be represented with a number (from 1 to 7) or with a name. A group of 28, 29, 30, or 31 consecutive days is called a month. In some financial situations, a group of 360 consecutive days is called a year.  In most calendars, a year can have 365 days every year except that, after 4 years, the year would have 366 days. This is referred to as a leap year. A group of 100 years is called a century. The years in a century can be identified with 2 or 4 digits, from 0 to 9999. A group of 1000 years is called a millennium. The years in a millennium must each be identified with 4 digits. Examples are 1608, 1978, or 2118. Within a year, each day can be identified by a numeric value.

To help manage the days of a year, a year is divided in 12 consecutive units. Each unit is 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.

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.

The combination of a day, month, and year is called a date.

Practical Learning: Introducing Date Values

1. Start Microsoft SQL Server Management Studio and click Connect
2. In the Object Explorer, right-click the name of the server and click New Query
3. On the Taskbar, click Start -> All Programs -> Accessories -> Windows Powershell -> Windows Powershell

A Date as a Data Type

To support dates, Transact-SQL provides a data type named DATE. This data type counts dates starting from January 1st, 0001 up to December 31st, 9999.

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:

Creating a Date Value

To start a date value, you can declare a variable of type DATE. Here is an example:

DECLARE @OneDay DATE;

To initialize the variable, you can assign a string to it. The string can contain a long natural number. Here is an example:

DECLARE @OneDay DATE = N'10360610';

Selecting a Date Value

After providing a date value in a variable, you can use a PRINT() function or a SELECT statement to read or display that value. Here is an example:

DECLARE @OneDay DATE = N'10360610';

PRINT CONCAT('Some date value: ', @OneDay);
GO

This would produce:

Some date value: 1036-06-10

Fondamental SQL Functions for Date Values

Parsing an Expression or Value

Parsing consists of scanning, that is, checking every part (such as every symbol) of an expression or a word to match a pattern. An example of parsing is to check every symbol in a combination to find out if it is a number or something else. Based on this, parsing something like 275.25 would determine whether this is a decimal number or not.

To assist you with parsing, Transact-SQL provides a function named PARSE. Its syntax is:

PARSE ( string_value AS data_type [ USING culture ] )

This function takes one argument, passed as a string and accompanied by its data type preceded by the AS keyword. The function is used to "scan" an expression that is passed as the string_value argument. The expression must follow the rules of the data_type argument. For example, suppose you want to find out if some value is an integer. You can pass the data_type as int. Then, the expression passed as the argument must include digits and no other character or symbol. On the other hand, if you want to know whether some value is a date, you can pass the data_type as date (or datetime2) and pass that value as argument. In this case, the value must follow the rules of a date value as specified in the Date tab in the Customize Regional Options accessible from the Regional and Language Options of the Control Panel.

If the argument may include international characters or formats (Unicode), you should indicate the language, called a culture, that the argument follows.

If the PARSE() function is not able to determine the type or if the value of the argument doesn't follow the rule of the data_type, this function produces (throws) an error. As an alternative to PARSE(), Transact-SQL provides the TRY_PARSE() function. Its syntax is:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

As you can see, this function uses the same argument as PARSE. The difference is that, while PARSE() produces an error if the parsing operation fails, TRY_PARSE produces NULL (if the parsing operation fails). This means that, in most cases, you should prefer TRY_PARSE() instead of PARSE().

Casting a Value

In most cases, a value the user submits to your database is primarily considered a string. This is convenient if that's what you are expecting. If the value the user provides must be treated as something other than a string, for example, if the user provides a number, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type.

To assist you to convert a value, Transact-SQL provides the CAST() function. Its syntax is:

CAST ( Expression AS DataType [ ( length ) ] )

The Expression is the value that needs to be cast. DataType is the type of value into which you want to convert the Expression. The DataType can be one of those we reviewed in Lesson 4.

If the CAST() function is not able to cast the expression (if it fails), it produces (throws) an error. As an alternative to CAST(), Transact-SQL provides a function named TRY_CAST. Its syntax is:

TRY_CAST ( expression AS data_type [ ( length ) ] )

This function takes the same arguments as CAST. The difference is that, instead of producing an error, if TRY_CAST() fails, it returns NULL. This also means that, in most cases, you should use TRY_CAST() instead of CAST.

Practical Learning: Casting a Value

1. Click inside the Query Editor and press Ctrl + A to select everything
2. Type the following:
   

   DECLARE @StrSalary nvarchar(10),
   	@StrHours  nvarchar(6),
   	@WeeklySalary decimal(6,2)
   SET @StrSalary = N'2W2.18';
   SET @StrHours  = N'38.50';
   
   SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
   		    CAST(@StrHours As Decimal(6,2));
   SELECT @WeeklySalary;
   GO

3. Press F5 to execute

   

4. To avoid the error, change the statement as follows:
   

   DECLARE @StrSalary nvarchar(10),
   	@StrHours  nvarchar(6),
   	@WeeklySalary decimal(6,2)
   SET @StrSalary = N'2W2.18';
   SET @StrHours  = N'38.50';
   
   SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
   		    CAST(@StrHours As Decimal(6,2));
   SELECT @WeeklySalary;
   GO

5. Press F5 to execute

   

6. Again, change the statement as follows:

   DECLARE @StrSalary nvarchar(10),
   	@StrHours nvarchar(6),
   	@WeeklySalary decimal(6,2)
   SET @StrSalary = N'242.18';
   SET @StrHours  = N'38.50';
   
   SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
   	            TRY_CAST(@StrHours As Decimal(6,2));
   SELECT @WeeklySalary;
   GO

7. Press F5 to execute

   

Converting a Value

Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT() can be used to convert a value from its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa.

The syntax of the CONVERT() function is:

CONVERT(DataType [ ( length ) ] , Expression [ , style ])

The first argument must be a known data type, such as those we reviewed already. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters in the data type's own parentheses, as the length argument. As reviewed for the CAST() function, the Expression is the value that needs to be converted. If the conversion is performed on a date or time value, the style argument is a number that indicates how that conversion must proceed. If the CONVERT() function is not able to perform its operation, it produces an error.

Because of some difficulties that can result from conversion, Transact-SQL provides a function named TRY_CONVERT. Its syntax is:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

The arguments are the same used in the CONVERT() function. The difference is that if the TRY_CONVERT() function fails, it returns NULL instead of producing (throwing) an error.

Practical Learning: Converting a Value

1. Click inside the Query Editor and press Ctrl + A
2. Type the following:
   

   -- Square Calculation
   DECLARE @Side As decimal(10,3),
           @Perimeter As decimal(10,3),
           @Area As decimal(10,3);
   SET     @Side = 48.126;
   SET     @Perimeter = @Side * 4;
   SET     @Area = @Side * @Side;
   PRINT N'Square Characteristics';
   PRINT N'-----------------------';
   PRINT N'Side      = ' + TRY_CONVERT(varchar(10), @Side, 10);
   PRINT N'Perimeter = ' + TRY_CONVERT(varchar(10), @Perimeter, 10);
   PRINT N'Area      = ' + TRY_CONVERT(varchar(10), @Area, 10);
   GO

3. Press F5 to execute

Introduction to Date Formats

Introduction

The number we provided for a date is a valid date value, but it can be difficult to read. As a better option, the value you provide in the single-quotes should use one of the following formulas:

YYYYMMDD
YYYY-MM-DD
MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY
DD-MMM-YY
DD-MMMM-YY
DD-MMM-YYYY
DD-MMMM-YYYY

The Year, the Month, and the Day as One Unit

You can start the value of a date 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. 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 is:

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

This would produce:

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

Practical Learning: Using Date Variables

1. In the Query Editor, type the following:

   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

2. To execute, press F5:

   

3. Click inside the Query Editor and press Ctrl + A
4. Type the following:

   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

5. Execute the statement:

   

A Date-Based Column

If you are visually creating a column and you want it to use date values, select date in the Data Type combo box of the column:

To create a date-based column with SQL code, apply the DATE data type.

We continue our university database. The school administers the courses in three parts of a year. Each part is called a semester, which is not literally equivalent to a real 6-month semester of a year. This means that, as opposed to a real year that contains two semesters, a school year has three semesters: The first semester occurs in the spring. It starts in the beginning of January and ends in the middle of May The second semester in the summer starts around the end of May and ends around the end of July The last semester is in the fall. It starts around the end of August and ends in the middle-end of December In this version, University5, we will create a table for the semesters.

Practical Learning: Creating Date-Based Columns

1. To create a new database, in the empty window, type the following:

   USE master;
   GO
   DROP DATABASE University4;
   GO
   CREATE DATABASE University5;
   GO
   USE University5;
   GO
   CREATE SCHEMA Academics;
   GO
   CREATE SCHEMA Administration;
   GO

2. To execute the SQL statement, press F5
3. In the Object Explorer, right-click Databases and click Refresh
4. Expand the University5 node
5. To start a new table, in the Object Explorer, right-click Tables and click New Table...
6. In the Properties window, click Schema and select Academics
7. Create the columns of the table as follows:

   Coloumn Name	Data Type	Allow Nulls
   Semester	nvarchar(30)	Unchecked
   SemesterStart	date	Unchecked
   SemesterEnd	date	Unchecked
   Session1Start	date	Unchecked
   Session1End	date	Unchecked
   Session2Start	date	Unchecked
   Session2End	date	Unchecked
   OnlineStart	date	Unchecked
   OnlineEnd	date	Unchecked

8. Close the table
9. When asked whether you want to save, click Yes
10. Set the name to Semesters
11. Click OK
13. In the PowerShell, type the following (If PowerShell doesn't work or you have any problem using it, in the Query Editor of the SQL Server Management Studio, type the following:
15. DepartmentStore2

    CREATE DATABASE DepartmentStore1;
    GO
    USE DepartmentStore1;
    GO
    CREATE SCHEMA Inventory;
    GO
    CREATE TABLE Inventory.StoreItems
    (
    	ItemNumber int,
    	DateEntered date,
    	Manufacturer nvarchar(50),
    	Category nvarchar(32),
    	SubCategory nvarchar(40),
    	ItemName nvarchar(80),
    	ItemSize nvarchar(32),
    	UnitPrice money,
    	DiscountRate decimal(6, 2)
    );
    GO

Data Entry With a Date-Based Column

A date-based column uses various formulas for its values. Data entry depends on whether you are working visually or with SQL code. If you are working in a Table window, to specify the value of a DATE-based field, use one of the following formulas:

MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY
DD-MMM-YY
DD-MMMM-YY
DD-MMM-YYYY
DD-MMMM-YYYY
YYYY-MM-DD

If you are performing data entry programmatically, use one of the above formulas plus the following:

YYYYMMDD

Practical Learning: Creating Date-Based Values

1. In the Object Explorer, right-click Tables and click Refresh
2. Expand Tables. Right-click Academics.Semesters and click Edit Top 200 Rows
3. Click under Semester and type SPRING2012 and press Tab
4. In the SemesterStart box, to follow the MM-DD-YY formula, type 01-09-12 and press Tab
5. In the SemesterEnd box, to follow the MM/DD/YY formula, type 04/29/12 and press Tab
6. To follow the DD-MMM-YY formula, type 09-JAN-12 and press Tab
7. To follow the YYYYMMDD formula, type 20120219 and press Tab.
   Notice that you receive an error
    
   
8. Click OK on the error message box
9. Complete the record with the following values:
   Semester: SPRING 2012
   SemesterStart: 2012-01-09
   SemesterEnd: 2012-04-29
   Session1Start: 2012-01-09
   Session1End: 19-FEBRUARY-12
   Session2Start: 2/27/2012
   Session2End: 29-APR-2012
   OnlineStart: 9-January-12
   OnlineEnd: 18-March-2012
10. Close the table
11. In the SQLQuery1, type the following code to create some records:

    USE University5;
    GO
    INSERT INTO Academics.Semesters(Semester, SemesterStart, SemesterEnd, Session1Start, Session1End, Session2Start, Session2End, OnlineStart, OnlineEnd)
    VALUES(N'SUMMER 2012', N'05-14-12', N'08-19-2012', N'05/14/12', N'07/08/2012', N'16-JUL-12', N'19-JULY-2012', N'15-MAY-12', N'07-AUGUST-2012'),
          (N'FALL 2012',   N'20120917', N'20121216', N'20120920', N'20121014', N'20121022', N'20121216', N'20120920', N'20121014'),		
          (N'SPRING 2013', N'20130107', N'20130428', N'20130107', N'20130217', N'20130225', N'20130428', N'20130107', N'20130317'),
          (N'SUMMER 2013', N'20130513', N'20130818', N'20130513', N'20130707', N'20130715', N'20130818', N'20130513', N'20130707'),
          (N'FALL 2013',   N'20130916', N'20131215', N'20130919', N'20131013', N'20131021', N'20131215', N'20130919', N'20131013');
    GO

12. To execute, on the main menu, click Query -> Execute
13. In the Object Explorer, under University5, right-click Academics.Semesters and click Edit Top 200 Rows
14. On the Query Designer toolbar, click the Show Diagram Pane button , the Show Criteria Pane button , and the Show SQL Pane button

Data Analysis On Date Values

   

Sorting Date Fields

If you sort records from a column that uses a date-based data type, if the column has null sections, the records with null would show first. The values of a date field are sorted in chronological order. If the values use only date values, the records that occur first would come up first. If two records have the same value, the record that was created first would also come before the other.

Practical Learning: Sorting Date Fields

1. In the Criteria pane, click the box at the intersection of SemesterStart and Sort Type, select Ascending
2. Right-click somewhere in the window and click Execute SQL
    
   
3. Click the SQLQuery1.sql tab and press Ctrl + A
4. Type the following:

   SELECT ALL sems.*
   FROM Academics.Semesters sems
   ORDER BY Session1Start;
   GO

5. Right-click anywhere in the window and click Execute SQL

   

Filtering Date-Based Columns

Remember that Transact-SQL supports all types of logical operators to perform comparisons. These can be done of date values:

• When the equality comparison is performed on dates, the values must occur on the exact same day to get a true value. Otherwise, the result is false
• When the inequality comparison is performed on dates, the values must occur on different days, which would produce true
• When the comparison for lower (or greater) value is performed on two date values, the first date must occur before (or after) the second for the comparison to produce a true value
• When the comparison for lower (or greater) or equal value is performed on two date values, the first date must occur either before (or after) the second date or both dates must occur on the same day. Such a comparison would produce a true value
• You can perform comparison for Not Greater Than operation, !>, on date values. This is the same as the Less Than Or Equal To operator "<="
• The comparison for Not Less Than, !<, operation is available on dates It is the same as >=

You can use any of the filter opeator we saw, such as BETWEEN or IN. You can also use conjunctions, disjunctions, and their combinations. You can also use a built-in function with any of the operators we know already. Here is an example:

SELECT FirstName, LastName, Gender, DateOfBirth, SingleParentHome
FROM   Registration.Students
WHERE  (DateOfBirth BETWEEN TRY_CONVERT(date, N'1995-01-01', 102) AND
			    TRY_CONVERT(date, N'1999-12-31', 102))

Practical Learning: Checking a Condition

1. Click inside the Query Editor and press Ctrl + A
2. In the Query Editor, type the following:
   

   DECLARE @DateHired As date,
   	@CurrentDate As date
   SET @DateHired = N'1996/10/04'
   SET @CurrentDate  = N'2007/04/11'
   IF @DateHired < @CurrentDate
       PRINT N'You have the experience required for a new promotion in this job';
   GO

3. To execute the statement, press F5:

 

1. Change the statement in the Query Editor as follows:

   DECLARE @DateHired As date,
   	@CurrentDate As date
   SET @DateHired = N'1996/10/04'
   SET @CurrentDate  = N'2007/04/16'
   IF @DateHired > @CurrentDate
   	PRINT N'You have the necessary experience for a new promotion.'
   GO

2. To execute, press F5. This would produce:

   

Introduction to Date Values and Functions

 

Overview

You have the option to create your own functions to use in a database. Still, Transact-SQL provides many built-in functions that you should first investigate before creating your own.

You can involve a function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

You can also include a function in any of the operators we have reviewed so far. You can also involve a built-in function in an expression.

To refine your data analysis, you can use functions, whether functions you create yourself or Transact-SQL built-in functions. As always mentioned, the first candidates of functions you should use are the built-in functions.

To use a built-in function, in the placeholder of the column, type the name of the function, followed by its parentheses. If the function takes some parameters, remember to follow the rules to call a parameterized function.

Creating a Date

We saw that there are so many ways to show a date value it can be confusing. In the same way, it can be confusing to compose a date. For example, what does 04-08-01 represents? It could be April 7, 2001. I could be August 4, 2001. I could be August 1st, 2004. To assist you with formulating a date by specifying a day, a month, and a year, Transact-SQL provides a function named DATEFROMPARTS. Its syntax is:

DATE DATEFROMPARTS(int year, int month, int day)

This function allows you to create a date if you have the year, the month, and the day.

Practical Learning: Introduction Date/Time Values

1. Click inside the Query Editor and press Ctrl + A
2. To use the DATEFROMPARTS() function, type the following:
   

   USE University5;
   GO
   INSERT INTO Academics.Semesters(Semester, SemesterStart, SemesterEnd,
   				Session1Start, Session1End, Session2Start, Session2End, 
   			        OnlineStart, OnlineEnd)
   VALUES(N'SPRING 2014',
          DATEFROMPARTS(2014, 1, 6), DATEFROMPARTS(2014, 04, 27), DATEFROMPARTS(2014, 01, 06),
          N'20140216', N'20140224', N'20140427', N'20140106', N'20140323'),
         (N'SUMMER 2014',
          DATEFROMPARTS(2014, 5, 12), DATEFROMPARTS(2014, 08, 17), DATEFROMPARTS(2014, 05, 12),
          N'20140706', N'20140714', N'20140810', N'20140512', N'20140706'),
         (N'FALL 2014',   
          DATEFROMPARTS(2014, 09, 15), DATEFROMPARTS(2014, 12, 21),DATEFROMPARTS(2014, 09, 25), 
          N'20141013', N'20141020', N'20141221', N'20140925', N'20141013');
   GO

3. To execute the SQL statement, press F5
4. Click include the Query Editor and press Ctrl + A
5. Type the following:

   SELECT sems.Semester,
          sems.SemesterStart,
          sems.SemesterEnd,
          sems.Session1Start,
          sems.Session1End,
          sems.Session2Start,
          sems.Session2End,
          sems.OnlineStart,
          sems.OnlineEnd
   FROM Academics.Semesters sems
   ORDER BY sems.SemesterStart;
   GO

6. Right-click anywhere in the window and click Execute SQL

The Current System Date

To let you get the current date (and time) of the computer that a user is using, Transact-SQL provides a function named GETDATE. Its syntax is:

GETDATE();

To get a date with more precision, call the SYSDATETIME function. Its syntax is:

SYSDATETIME();

Imagine you have a database named AutoRepairShop and it has a table used to create repair orders for customers:

CREATE TABLE RepairOrders
(
  ReceiptNumber int,
  CustomerName varchar(50),
  CustomerPhone varchar(20),
  RepairDate datetime2
);
GO

When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the SYSDATETIME() or the GETDATE() function. Here are examples:

INSERT INTO RepairOrders(ReceiptNumber, CustomerName, CustomerPhone, RepairDate)
	    VALUES(100001, N'Annette Berceau', N'301-988-4615', SYSDATETIME());
GO
INSERT INTO RepairOrders(ReceiptNumber, CustomerPhone, CustomerName, RepairDate)
	    VALUES(100002, N'(240) 601-3795', N'Paulino Santiago', SYSDATETIME());
GO
INSERT INTO RepairOrders(ReceiptNumber, CustomerName, RepairDate, CustomerPhone)
	    VALUES(100003, N'Alicia Katts', SYSDATETIME(), N'(301) 527-3095');
GO
INSERT INTO RepairOrders(ReceiptNumber, RepairDate, CustomerPhone, CustomerName)
	    VALUES(100004, SYSDATETIME(), N'703-927-4002', N'Bertrand Nguyen');
GO

Converting a String to Date

As mentioned already, to let you convert a string to a date value, Transact-SQL provides the TRY_PARSE() and TRY_CONVERT() functions. These functions take a string as argument and they scan that argument. If it is a valid date, the function returns it. If not, the functions returns NULL.

Here is an example:

SELECT FirstName, LastName, Gender, DateOfBirth, SingleParentHome
FROM   Registration.Students
WHERE  (DateOfBirth BETWEEN TRY_CONVERT(date, N'1995-01-01', 102) AND
			    TRY_CONVERT(date, N'1999-12-31', 102))

Formatting and Controlling the Display of Dates

Introduction

Transact-SQL and Microsoft Windows provide extensive support in the way a variable produces, or a column displays, the value of a date. In Transact-SQL, this is done using the the FORMAT() function. Its syntax is:

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

This function takes two required arguments. The third is optional. If you are formatting a date, the first argument must have the original value. The second argument specifies how the formatting must be carried. A date value is made of one or many sections. If it contains many sections, they are separated by symbols, almost any, such as a period or a comma. In US English, the standard separators are / (forward slash) or - (dash). We will review the letters to use and their combinations.

Displaying the Numeric Day

The days of months are numbered from 1 to 31, depending on the month. One way you can display the day is to use dd (lowercase):

• One way to display the day of the month is to use d (lowercase) in a combination that includes the month and the year. In this case, d produces the day in 1 digit if the number is between 1 and 9 (no leading 0) or in 2 digits
• Whether passed by itself or in an expression that contains the other parts, dd produces the day in 2 digits. If the number is between 1 and 9, it displays with a leading 0. Here is an example:

  DECLARE @DateValue DATE,
          @StrValue nvarchar(50);
  SET @DateValue = N'20120604';
  SET @StrValue = FORMAT(@DateValue, N'dd');
  SELECT @DateValue AS Original;
  SELECT @StrValue AS Formatted;
  GO

  This would produce:

  

Displaying Weekday Names

The names of the week use two formats: 3 letters or full name. To display a name with 3 letters, use "ddd" in the format argument. The names will be Sun, Mon, Tue, Wed, Thu, Fri, or Sat. Here is an example:

DECLARE @DateValue DATE,
@StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'ddd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

To display the complete name of a weekday, pass "dddd"� by itself or include it in a complete format string. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'dddd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

To display the weekday and the numeric day of the month, you can create a format that combines both constants. When creating this string, the constants must be separated inside the string so the compiler would know which format to apply and where. To separate the formats, you can use (almost) any character but you should conform to those used in your regional settings. One of the most regularly used separators on dates is the comma but the simplest separator is an empty space. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'ddd dd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

Practical Learning: Displaying Weekdays

1. Change the statement in the Query Editor as follows:

   SELECT sems.Semester,
          FORMAT(sems.SemesterStart, N'dddd') "Semester starts on",
          sems.SemesterStart, 
          sems.SemesterEnd, 
          FORMAT(sems.SemesterEnd, N'dddd') "Semester ends on",
          sems.Session1Start, sems.Session1End, sems.Session2Start, 
          sems.Session2End, sems.OnlineStart, sems.OnlineEnd
   FROM Academics.Semesters sems
   ORDER BY sems.SemesterStart;
   GO

2. Right-click anywhere in the window and click Execute SQL

Displaying Numeric Months

Months are displayed using two categories: a number or a name. The months are numbered as follows: 1=January, 2=February, 3=March, 4=April, 5=May, 6=June, 7=July, 8=August, 9=September, 10=October, 11=November, and 12=December.

To display only the number of the month, pass the format argument as MM (uppercase). In this case, the month is provided as an integer with 2 digits. If the number is between 1 and 9, it displays with a leading 0. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'MM');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

If you are passing an expression that should produce at least one more part such as the day, you can include M (uppercase). The difference becomes as follows:

• M (uppercase) would produce the number of the month without the leading 0. Here is an example:

  DECLARE @DateValue DATE,
          @StrValue nvarchar(50);
  SET @DateValue = N'20120604';
  SET @StrValue = FORMAT(@DateValue, N'd-M');
  SELECT @DateValue AS Original;
  SELECT @StrValue AS Formatted;
  GO

  This would produce:

  

• MM (uppercase) would produce the number of the month with 2 digits. If the number is between 1 and 9, it displays a leading 0

Following the rules we applied to display a combination of a weekday and a month's numeric day, you can display a month and the month's numeric value. This time, instead of an empty space, you should use a character that would indicate that the date is displaying a combination of month and day (or day and month). The common character to use is the one that conforms to the regional settings of your computer. In the US English, this would be the forward slash "/"�. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'M/dd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

In the same way you can combine a weekday (short or long name) followed by the combination of day/month (or month/day) as you see fit. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'ddd M/dd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

You can also use this format when constructing a combined date:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'dddd dd-MM');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

Displaying the Name of a Month

You can display a month by its name using one of two formats: short or long name. The short names of months are: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec. To display the short name of a month, pass the format as MMM (uppercase). You can pass MMM by itself to get just the short name of the month. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'MMM');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

As an alternative, you can include MMM in a combination that produces the other parts. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20120604';
SET @StrValue = FORMAT(@DateValue, N'dddd, dd-MMM');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

The long names of months are January, February, March, April, May, June, July, August, September, October, November, or December. To display the long name of a month, pass MMMM (uppercase) as the format. You can pass this constant as itself or along with the other parts. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'dddd, d MMMM');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

Practical Learning: Displaying Months

1. Change the statement in the Query Editor as follows:

   SELECT sems.Semester,
          FORMAT(sems.SemesterStart, N'MMMM') "Semester spans from",
          FORMAT(sems.SemesterEnd, N'MMMM') "Semester spans to",
          FORMAT(sems.SemesterStart, N'dddd') "Semester starts on",
          sems.SemesterStart, 
          sems.SemesterEnd, 
          FORMAT(sems.SemesterEnd, N'dddd') "Semester ends on",
          sems.Session1Start, sems.Session1End, sems.Session2Start, 
          sems.Session2End, sems.OnlineStart, sems.OnlineEnd
   FROM Academics.Semesters sems
   ORDER BY sems.SemesterStart;
   GO

2. Right-click anywhere in the window and click Execute SQL

Displaying the Year of a Date

If you want to display the year in two digits, pass yy (lowercases) as the format. When used by itself, yy produces the year of the date argument. The year is provided with 2 digits. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20060604';
SET @StrValue = FORMAT(@DateValue, N'yy');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

In a combination with the day, the month, and the year, use yy to display the year with 2 digits. Examples are 06 or 14 or 68. We have already reviewed how the number is interpreted.

Whether passed by itself or in an expression that contains the other parts, yyy (lowercase) or yyyy (lowercase) produces a year in 4 digits.

From what we have seen so far, you can display a complete date using any format of your choice, such as the day, the month, and the year. Here is an example that shows the short weekday, the short name of the month, and the 2-digit year:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'ddd dd MMM yy');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

Here is an example the displays the long name of the day and the long name of the month:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'dddd d MMMM yy');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

A year value represented with two digits is hardly explicit, unless you have a good reason for using it. The alternative is to use all four digits to display a year. This format is created with the yyy (lowercase) or yyyy (lowercase). Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'yyy');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

Now we know how to create a short or a complete date value using the appropriate letters and the desired combination. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'dddd, MMMM dd, yyyy');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

Practical Learning: Displaying the Year of a Date

1. Change the statement in the Query Editor as follows:

   SELECT sems.Semester,
          FORMAT(sems.SemesterStart, N'yyyy') Year,
          FORMAT(sems.SemesterStart, N'MMMM') "Semester spans from",
          FORMAT(sems.SemesterEnd, N'MMMM') "Semester spans to",
          FORMAT(sems.SemesterStart, N'dddd') "Semester starts on",
          sems.SemesterStart, 
          sems.SemesterEnd,
          sems.Session1Start, sems.Session1End, sems.Session2Start, 
          sems.Session2End, sems.OnlineStart, sems.OnlineEnd
   FROM Academics.Semesters sems
   ORDER BY sems.SemesterStart;
   GO

2. Right-click anywhere in the window and click Execute SQL

The Month and Year Format

You can pass the format as m or M. If you pass only m or M as the format, the function produces the long name of the month followed by the year in 2 digits. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20060604';
SET @StrValue = FORMAT(@DateValue, N'm');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

As an alternative, you can pass the format as y (lowercase) or Y (uppercase) only. In this case, the function would produce the full name of the month, followed by a comma, and the year in 4 digits:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20121004';
SET @StrValue = FORMAT(@DateValue, N'y');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

The Short Date Format

The standard way to display a date is called the short date format. It is defined in the Short Date section of the Date property page of the Customize Regional Options accessible from the Regional and Language Options of the Control Panel:

To display a date in the short date format, pass the second value as d (lowercase). In US English, the short date format is M/d/yyyy. The month is provided in digits. If the month and the day use a number from 1 to 9, it doesn't use a leading 0. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20060604';
SET @StrValue = FORMAT(@DateValue, N'd');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

If d (lowercase) is used in a combination with letters for the month and year, the day is provided as 1 (for days from 1 to 9) or 2 (for days from 1 to 12) digits.

An alternative is to pass G (uppercase) as the format argument:

• If G is passed by itself, the FORMAT() function produces the date in the short name format, followed by 12:00:00 AM
• G (uppercase) should not be passed with other letters and symbols

Another alternative is to pass the format as g (lowercase):

• If g is passed by itself, the function produces the date in the short name format, followed by 12:00 AM
• If g is passed with other letters and symbols, the g placeholder shows A.D. if the year happens after 0000

The Long Date Format

Another common way to display a date is called the long date format. It is defined in the Long Date section of the Date property page of the Customize Regional Options. To display a date in the long date format, pass the second value as D (uppercase). In US English, the short date format is dddd, MMMM dd, yyyy. The weekday and the month are provided in full names. The day is provided with a leading 0 if its number is between 1 to 9. Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20060604';
SET @StrValue = FORMAT(@DateValue, N'D');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

D (uppercase) should not be used in a combination with letters for the month, the day, and the year.

An alternative is to use f (lowercase). If f is passed by itself, the function produces the long date format followed by 12:00 AM Here is an example:

DECLARE @DateValue DATE,
        @StrValue nvarchar(50);
SET @DateValue = N'20060604';
SET @StrValue = FORMAT(@DateValue, N'f');
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

This would produce:

The letter f should not be used along with the other letters and symbols. One more alternative is to pass F (uppercase) as argument:

• If F is passed by itself, the function produces the long date format followed by 12:00:00 AM
• F should not be used along with the other letters and symbols

Practical Learning: Showing Long Date Formats

1. Change the statement in the Query Editor as follows:

   SELECT sems.Semester,
          FORMAT(sems.SemesterStart, N'yyyy') Year,
          FORMAT(sems.SemesterStart, N'MMMM') "Semester spans from",
          FORMAT(sems.SemesterEnd, N'MMMM') "Semester spans to",
          FORMAT(sems.SemesterStart, N'D') "Start of Semester",
          FORMAT(sems.SemesterEnd, N'D') "End of Semester",
          FORMAT(sems.Session1Start, N'D') "Start of Session 1", 
          FORMAT(sems.Session1End, N'D') "End of Session 1", 
          FORMAT(sems.Session2Start, N'D') "Start of Session 2", 
          FORMAT(sems.Session2End, N'D') "End of Session 2", 
          FORMAT(sems.OnlineStart, N'D') "End of Online Sessions"
   FROM Academics.Semesters sems
   ORDER BY sems.SemesterStart;
   GO

2. Right-click anywhere in the window and click Execute SQL

Operations on Dates

   

Adding a Value to a Date

Date addition consists of adding a number of days, a number of months, or a number of years, to a date value. The function used to perform this operation is DATEADD. Its syntax is:

DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)

The first argument specifies the type of value that will be added. It can be one of the following values:

 

The second argument is the number of TypeOfValue to be added. It should be a constant integer. The third argument is the original value on which the operation will be performed. Here is an example that adds a number of years to a date value:

DECLARE @Original date,
	@Result date;
SET     @Original = N'20121204';
SET     @Result = DATEADD(yy, 2, @Original);
SELECT @Original [Original Date];
SELECT @Result [2 Years Later];
GO

This would produce:

Here is an example that adds 2 quarters to a date:

DECLARE @Original date,
	@Result date;
SET     @Original = N'20121204';
SET     @Result = DATEADD(Quarter, 2, @Original);
SELECT @Original [Original Date];
SELECT @Result [2 Years Later];
GO

This would produce

Here is an example that adds 5 months to a date:

DECLARE @Original date,
	@Result date;
SET     @Original = N'20121004';
SET     @Result = DATEADD(m, 5, @Original);
SELECT @Original [Original Date];
SELECT @Result [2 Years Later];
GO

This would produce

Practical Learning: Adding a Value to a Date

1. Click inside the the Query Editor and press Ctrl + A
2. Type the following:

   /* Instead of specifying a deterministic date of birth, we will supply 
      a number of days to this function and, based on day this script is 
      run, the function will subtract the number of days from that date.
      That's how we will get the date of birth of a student. */
   
   CREATE FUNCTION Administration.SetDateOfBirth(@days int)
   RETURNS Date
   AS
   BEGIN
   	RETURN DATEADD(d, @days, SYSDATETIME());
   END
   GO
   CREATE TABLE Academics.UndergraduateStudents
   (
       StudentNumber nvarchar(8) not null,
       FirstName nvarchar(20),
       MiddleName nvarchar(20),
       LastName nvarchar(20),
       BirthDate date,
       Gender nvarchar(5) default N'N/A'
   );
   GO
   INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender)
   VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F'),
         (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M');
   GO
   INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender)
   VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M');
   GO
   INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate)
   VALUES(N'94759284', N'Chris', N'Harpers', Administration.SetDateOfBirth(-10570));
   GO
   INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender)
   VALUES(N'22803048', N'Gary', N'Jonathan', N'Jones', Administration.SetDateOfBirth(-19926), N'M'),
         (N'97394285', N'Jessica', N'Danielle', N'Weisburgh', Administration.SetDateOfBirth(-12081), N'F'),
         (N'97596002', N'Laurent', N'Frank', N'Simonson', Administration.SetDateOfBirth(-17503), N'M'),
         (N'94708257', N'Christopher', N'Sheldon', N'Dale', Administration.SetDateOfBirth(-6570),  N'M'),
         (N'48009520', N'Diane', N'Kathy', N'Paglia', Administration.SetDateOfBirth(-13840), N'F'),
         (N'13048039', N'Joseph', N'Christian', N'Riback', Administration.SetDateOfBirth(-7909),  N'M'),
         (N'92270397', N'Patrick', N'Jonathan', N'Brzeniak', Administration.SetDateOfBirth(-17361), N'M');
   GO
   INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender)
   VALUES(N'70840584', N'Tracy', N'Sikorowski', Administration.SetDateOfBirth(-11650), N'M');
   GO

3. Right-click anywhere in the window and click Execute SQL

Finding the Difference of Two Date Values

To find the difference between two dates, you can call the DATEDIFF() function. Its syntax is:

DATEDIFF(TypeOfValue, StartDate, EndDate)

The first argument specifies the type of value the function must produce. This argument uses the same value as those of the DATEADD() function. The second argument is the starting date. The third argument is the end date. Here is an example that calculates the number of years that an employee has been with the company:

DECLARE @DateHired As date,
        @CurrentDate As date;
SET @DateHired = N'2005/10/04';
SET @CurrentDate  = N'20130622';
SELECT DATEDIFF(Year, @DateHired, @CurrentDate)
       AS [Current Experience];
GO

This would produce:

Immediate If

Consider the following table:

USE master
GO

IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'PublicLibrary1'
)
DROP DATABASE PublicLibrary1
GO
CREATE DATABASE PublicLibrary1
GO

USE PublicLibrary1;
GO

CREATE SCHEMA Administration;
GO
CREATE TABLE Administration.Members
(
    MemberNumber nchar(11),
    FirstName nvarchar(25),
    LastName nvarchar(25),
    Gender int,
    DateOfBirth date
);
GO

INSERT INTO Administration.Members
VALUES(N'93757-49411', N'Joshua', N'Ransome', 1, N'20020412');
GO
INSERT INTO Administration.Members(MemberNumber, FirstName, LastName, Gender)
VALUES(N'20304-70592', N'Chris', N'Drews', 3);
GO
INSERT INTO Administration.Members
VALUES(N'13594-60857', N'Ephraim', N'Chance', 1, N'19440824'),
	  (N'29283-47597', N'Jeannette', N'Gong', 2, N'19781104');
GO
INSERT INTO Administration.Members(MemberNumber, FirstName, LastName, DateOfBirth)
VALUES(N'39485-00741', N'Bobbie', N'Womack', N'19840602');
GO
INSERT INTO Administration.Members(MemberNumber, FirstName,
		LastName, Gender, DateOfBirth)
VALUES(N'83758-30737', N'Mathilda', N'Bambeck', 2, N'19940816');
GO

Here is a list of the records:

USE PublicLibrary1;
GO

SELECT mbrs.MemberNumber [Mbr #],
       mbrs.FirstName [First Name],
       mbrs.LastName [Last Name],
       mbrs.Gender,
       mbrs.DateOfBirth [Date of Birth],
       DATEDIFF(Year, mbrs.DateOfBirth, SYSDATETIME()) Age
FROM Administration.Members mbrs;
GO

This would produce:

The immediate-if function, named IIF, is used to take action depending on the outcome of a condition. Its syntax is:

IIF(Conditional Expression, What To Do If True, What To Do If False) RETURNS sql_variant;

The IIF() function takes three arguments. The first is a conditional statement expressed using any of the logical operators we reviewed. The conditional statement is true, the function returns the second argument and stops. If the conditional statement is false, the function ignores the second argument and returns the third argument.

Here are examples of calling the IIF() function:

SELECT mbrs.MemberNumber [Mbr #],
       mbrs.FirstName [First Name],
       mbrs.LastName [Last Name],
       mbrs.Gender,
       mbrs.DateOfBirth [Date of Birth],
       DATEDIFF(Year, mbrs.DateOfBirth, SYSDATETIME()) Age,
       IIF(DATEDIFF(Year, DateOfBirth, SYSDATETIME()) <= 18, N'Teen', N'Adult') "Membership Category"
FROM Administration.Members mbrs;
GO

This would produce:

One way to expand the IIF() function is to nest one inside of another. To do that, you can replace one of the consequences  (What To Do If True or What To Do If False) with an IIF() function. Here are examples:

USE PublicLibrary1;
GO

SELECT mbrs.MemberNumber [Mbr #],
       mbrs.FirstName [First Name],
       mbrs.LastName [Last Name],
  IIF(mbrs.Gender = 1, N'Male', IIF(mbrs.Gender = 2, N'Female', N'Unknown')) Gender,
       mbrs.DateOfBirth [Date of Birth],
       DATEDIFF(Year, mbrs.DateOfBirth, SYSDATETIME()) Age,
       IIF(DATEDIFF(Year, DateOfBirth, SYSDATETIME()) <= 18, 
	   N'Teen',
	   IIF(DATEDIFF(Year, DateOfBirth, SYSDATETIME()) <= 65, 
	       N'Adult',
               N'Senior')) "Membership Category"
FROM Administration.Members mbrs;
GO

This would produce:

 

 

Practical Learning: Finding the Difference in Date Values

1. Click inside the Query Editor and press Ctrl + A
2. Type the following:

   SELECT Students.StudentNumber [Student #],
          Students.FirstName [First Name],
          Students.MiddleName [Middle Name],
          Students.LastName [Last Name],
          Students.BirthDate,
          DATEDIFF(yyyy, BirthDate, SYSDATETIME()) Age,
          Students.Gender
   FROM Academics.UndergraduateStudents Students;
   GO

3. To execute, press F5
4. Change the statement as follows:

   SELECT Students.StudentNumber [Student #],
          Students.FirstName [First Name],
          Students.MiddleName [Middle Name],
          Students.LastName [Last Name],
          FORMAT(Students.BirthDate, N'f'),
          DATEDIFF(yyyy, BirthDate, SYSDATETIME()) Age,
          CASE Students.Gender
   		WHEN N'M' THEN N'Male'
   		WHEN N'F' THEN N'Female'
   		ELSE N'Unknomn'
          END Gender
   FROM Academics.UndergraduateStudents Students;
   GO

5. To execute, press F5

   

Getting the Parts of a Date

   

Introduction to the Part Name of a Date Value

Transact-SQL provides various options to get the day, the month, or the year of an existing date value. The values are gotten using some functions. One of the functions used is named DATENAME. Its syntax is:

integer/nvarchar DATENAME(integer ReturnedValue, date Value);

ReturnedValue specifies the value to get from the date. The second argument is the date that holds the value from which the value will be produced. The date value must be a valid date. If the date value is not valid, the function would produce an error. For example, 20110229 would return an error since the month of February in 2011 did not have 29 days.

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

DATEPART(int DatePart, date Value)

 The Value argument uses the same constants as for the DATENAME() function. The constants provided for ReturnedValue or Value are not case-sensitive. This means that year, YEAR, and Year would work the same.

Transact-SQL provides additional functions to get the day, the month, and the year of a date.

Getting the Day of a Date

A typical date combines a day, a month, and a year. The day is a numeric value within the month. To get the day in the month of a date value, you have various options. Transact-SQL provides the DAY() function whose syntax is:

int DAY(date Value);

This function takes a date as argument and produces its day. Here is an example:

DECLARE @DateValue DATE,
        @Result int;
SET @DateValue = N'20121004';
SET @Result = DAY(@DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

As an alternative, you can call the DATENAME() function and pass the ReturnedValue as Day (or day), d, or dd. Here is an example:

DECLARE @DateValue DATE,
        @Result int;
SET @DateValue = N'20121004';
SET @Result = DATENAME(dd, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

One more alternative is to call the DATEPART() function and pass  Day (or day), d, or dd as the Value.  Here is an example:

DECLARE @DateValue DATE,
        @StrValue int;
SET @DateValue = N'20120425';
SET @StrValue = DATEPART(Day, @DateValue);
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

In this case, both functions produce the same result.

Getting the Month Name of a Date

A regular date contains a month. To get the numeric month of a date value, you have two options. You can use Transact-SQL's MONTH() function. Its syntax is:

int MONTH(date Value);

This function takes a date as argument and produces the month. Here is an example:

DECLARE @DateValue DATE,
        @Result int;
SET @DateValue = N'20121004';
SET @Result = MONTH(@DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

Notice that the MONTH() function returns an integer. Besides MONTH(), Transact-SQL provides the DATENAME() function. To get the month, pass the ReturnedValue as Month (or month), m, or mm. This means that you must consider the ReturnedValue as a string. Here is an example:

DECLARE @DateValue DATE,
        @Result nvarchar(30);
SET @DateValue = N'20121004';
SET @Result = DATENAME(mm, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

This would produce:

You can also use the DATEPART() function to get the month. In this case, you would pass the same argument as for the DATENAME() function..  Here is an example:

DECLARE @DateValue DATE,
        @StrValue int;
SET @DateValue = N'20120425';
SET @StrValue = DATEPART(Month, @DateValue);
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

Getting the Year of a Date

To let you get the year of a date, Transact-SQL provides the YEAR() function. Its syntax is:

int YEAR(date Value);

Here is an example:

DECLARE @DateValue DATE,
        @Result int;
SET @DateValue = N'20121004';
SET @Result = YEAR(@DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

This would produce:

Another way to get the year value is to call the DATENAME() function. In this case, pass the ReturnedValue argument as Year (or year), yy or, yyyy. In this case, the function returns an integer. Here is an example:

DECLARE @DateValue DATE,
        @Result int;
SET @DateValue = N'20121004';
SET @Result = DATENAME(yy, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Formatted;
GO

In the same way, you can call the DATEPART() function to get the year. You would pass the argument as yy or yyyy, just as done for the DATENAME() function..  Here is an example:

DECLARE @DateValue DATE,
        @StrValue int;
SET @DateValue = N'20120425';
SET @StrValue = DATEPART(mm, @DateValue);
SELECT @DateValue AS Original;
SELECT @StrValue AS Formatted;
GO

The result is the same.

Getting the Quarter of a Year

A year is made of four parts that each has 3 consecutive months. Each one of those parts is called a quarter. A quarter is represent with a small integer. The first quarter that contains January, February, and March has a value of 1. To get the quarter of a year of a date, you can call the DATENAME() or the DATEPART() function. In this case, pass the ReturnedValue or the Value argument as Quarter (or quarter), q or, qq. In both cases, the function returns an integer. Here is an example:

DECLARE @DateValue DATE,
        @Result nvarchar(30);
SET @DateValue = N'20120714';
SET @Result = DATENAME(q, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

This would produce:

Getting the Week of a Year

A year is divided in 52 or 53 parts named weeks. Each week is made of 7 consecutive days. A week is represented as a small integer. To get the numeric week of a date, call either the DATENAME() or the DATEPART() function. Pass the ReturnedValue or the Value argument as week, wk or, ww. Here is an example:

DECLARE @DateValue DATE,
        @Result nvarchar(30);
SET @DateValue = N'20121231';
SET @Result = DATENAME(Week, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

This would produce:

Getting the Weekday of a Week

As mentioned already, a week is made of 7 consecutive days and each or those days has a name.known as the weekday. To get the weekday of a date, call either the DATENAME() or the DATEPART() function. Pass the ReturnedValue or the Value argument as Weekday or dw. Here is an example:

DECLARE @DateValue DATE,
        @Result nvarchar(30);
SET @DateValue = N'20121231';
SET @Result = DATENAME(Weekday, @DateValue);
SELECT @DateValue AS Original;
SELECT @Result AS Result;
GO

This would produce:

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-2025, FunctionX	Last Update: 02 October 2012, 13:37	Next