Date Values

Fundamentals of Date Values

Introduction

If you have to use date values in a Microsoft SQL Server database graphically developed in (Microsoft Visual) C#, you have two main options and another secondary option:

• Transact-SQL provides a vast library of functions meant to create and manage date values
• The .NET Framework is equipped with a structure named DateTime. The C# language has a data type named string that is type-defined from the String class. The .NET Framework provides other structures (such as TimeStamp), interfaces (such as IFormatProvider and ICustomFormatter), and classes used for date values. These structures and classes provide many methods (member functions) you can use to perform various types of date-related operations
• The Visual Basic language provides a third option for date values. The language has its own library of functions that can be used to create and manipulate date values

To assist you with date values, Transact-SQL 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.

The primary rules of date values are defined in the Date tab of the Customize Regional Options accessible from Control Panel:

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
DD-MMM-YY
DD-MMMM-YY
DD-MMM-YYYY
DD-MMMM-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:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @OneDay DATE; " +
                                            "SET     @OneDay = N'10360610'; " +
                                            "SELECT  @OneDay; ",
                                            connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Day to Prevail: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

This would produce:

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. Here is an example:

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

Instead of providing the whole value in one combination of digits, you can use the second formula. 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:

void btnDatabaseClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("DECLARE @IndependenceDay date; " +
                                            "SET     @IndependenceDay = N'07/04/1776'; " +
                                            "SELECT  @IndependenceDay; ",
                                            connection);

        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();

        while (rdr.Read())
            MessageBox.Show("Independence Day: " + rdr[0].ToString(),
                            "Database Application",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
    }
}

This would produce:

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:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                                "SET     @SomeDate = N'5-7-05'; " +
                                                "SELECT  @SomeDate; ",
                                                connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/05'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5-7-41'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/41'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5-7-81'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("DECLARE @SomeDate date; " +
                                     "SET     @SomeDate = N'5/7/81'; " +
                                     "SELECT  @SomeDate; ",
                                     connection);

            connection.Open();
            SqlDataReader rdr = command.ExecuteReader();

            while (rdr.Read())
                MessageBox.Show("Some Date: " + rdr[0].ToString(),
                                "Database Application",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

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

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

Transact-SQL supports values of date types. In a graphical application, the value can be provided by a text box, a masked text box, a calendar, or a date picker. Fortunately, the .NET Framework easily communicates a date value to Transact-SQL with no configuration or very little concern. The value should be passed as a string.

As you may know already, the text box is the most fundamental control used to get values from a user. As such, you can request a date-based value from a user. A disadvantage of the text box is that it can receive any type of value. This means that there is no guaranty that the user would corectly enter a date value. This also means that you should make sure you validate the value the user would have entered.

An alternative to the text box is the masked text box. It also you to restrict the characters and digits the user can enter into the control so you can make sure the user types a date value.

After adding the masked text box to a form, in the Properties window, click Set Mask... In the Input Mask dialog box, select Short Date:

Then click OK. In reality, the masked text box only makes sure that some characters are not allowed in the control but it does not guarantee that the user can enter only a valid date. This means that, as mentioned for the text box, you must verify the value the user would have typed. If the user provides a bad value, the database engine would produce an error. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise2
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdExercise =
                    new SqlCommand("INSERT INTO Personnel.Employees " +
                                   "VALUES(N'" + mtbEmployeeNumber.Text + "', N'" +
                                   txtFirstName.Text + "', N'" + txtLastName.Text +
                                   "', N'" + DateTime.Parse(mtbDateHired.Text) + "');",
                                   cntExercise);

                cntExercise.Open();
                SqlDataReader rdrEmployees = cmdExercise.ExecuteReader();

                mtbEmployeeNumber.Text = "";
                txtFirstName.Text = "";
                txtLastName.Text = "";
                mtbDateHired.Text = "";
            }
        }
    }
}

This means that it is your job to check the value the user provides and/or to handle the exception. Here is an example:

private void btnSubmit_Click(object sender, EventArgs e)
{
    using (SqlConnection cntExercise =
        new SqlConnection("Data Source=(local);" +
                          "Database='Exercise1';" +
                          "Integrated Security=SSPI;"))
    {
        try
        {
            SqlCommand cmdExercise =
                new SqlCommand("INSERT INTO Personnel.Employees " +
                               "VALUES(N'" + mtbEmployeeNumber.Text + "', N'" +
                               txtFirstName.Text + "', N'" + txtLastName.Text +
                               "', N'" + DateTime.Parse(mtbDateHired.Text) + "');",
                               cntExercise);

            cntExercise.Open();
            SqlDataReader rdrEmployees = cmdExercise.ExecuteReader();

            mtbEmployeeNumber.Text = "";
            txtFirstName.Text = "";
            txtLastName.Text = "";
            mtbDateHired.Text = "";
        }
        catch (FormatException fe)
        {
            MessageBox.Show("The value you entered  is not in the correct format of a date value.",
                            "New Employee Registration",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

The calendar control is the best control to make sure the user provides a valid date. The control is configured to make sure that the user can only select a date and cannot just type anything. When the user has typed a date, you can simply get the value from the calendar control and pass it to the appropriate column.

Normally, a calendar control allows the user to select a range of date from a starting point to finish. When configuring a calendar control for a date-based column of a database table, it may be a good idea to allow only one value selection. This is done by setting the MaxSelectionCount property to 1. When the user has selected a date, you can get the value of the SelectionStart or the SelectionEnd property as the value for the column. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MaskedTextBoxDatabase
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdExercise =
                    new SqlCommand("INSERT INTO Personnel.Employees " +
                                   "VALUES(N'" + mtbEmployeeNumber.Text + "', N'" +
                                   txtFirstName.Text + "', N'" + txtLastName.Text +
                                   "', N'" + calDateHired.SelectionStart.ToShortDateString() + "');",
                                   cntExercise);

                cntExercise.Open();
                SqlDataReader rdrEmployees = cmdExercise.ExecuteReader();

                mtbEmployeeNumber.Text = "";
                txtFirstName.Text = "";
                txtLastName.Text = "";
                calDateHired.SelectionStart = DateTime.Today;
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Part of the date time picker is a replica of the calendar control. The advantage of the date picker is that it saves space. One of the differences between both controls is that the date picker is equipped with a property named Value that holds the selected date. Other than that, you can use the date picker control to get a value for a date-dased column. Here is an example:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Label lblEmployeeName;
    TextBox txtEmployeeName;
    Label lblDateHired;
    DateTimePicker dtpDateHired;
    Button btnCreateDatabase;
    Button btnAddEmployee;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateDatabase = new Button();
        btnCreateDatabase.Text = "Create Database";
        btnCreateDatabase.Location = new Point(12, 12);
        btnCreateDatabase.Width = 120;
        btnCreateDatabase.Click += new EventHandler(btnCreateDatabaseClick);

        lblEmployeeName = new Label();
        lblEmployeeName.AutoSize = true;
        lblEmployeeName.Text = "Employee Name:";
        lblEmployeeName.Location = new Point(12, 44);

        txtEmployeeName = new TextBox();
        txtEmployeeName.Location = new Point(110, 44);

        lblDateHired = new Label();
        lblDateHired.AutoSize = true;
        lblDateHired.Text = "Marital Status:";
        lblDateHired.Location = new Point(12, 66);

        dtpDateHired = new DateTimePicker();
        dtpDateHired.Location = new Point(110, 66);

        btnAddEmployee = new Button();
        btnAddEmployee.Text = "Add Employee";
        btnAddEmployee.Location = new Point(12, 100);
        btnAddEmployee.Width = btnCreateDatabase.Width;
        btnAddEmployee.Click += new EventHandler(btnAddEmployeeClick);

        Text = "Database Exercise";
        Controls.Add(lblEmployeeName);
        Controls.Add(txtEmployeeName);
        Controls.Add(lblDateHired);
        Controls.Add(dtpDateHired);
        Controls.Add(btnAddEmployee);
        Controls.Add(btnCreateDatabase);

        StartPosition = FormStartPosition.CenterScreen;
    }

    void btnCreateDatabaseClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE DATABASE Exercise1;", cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local); " +
                              "Database='Exercise1'; " +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE SCHEMA Personnel;",
                               cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1'; " +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE TABLE Personnel.Employees" +
                               "(" +
                               "    EmployeeName nvarchar(50)," +
                               "    DateHired date" +
                               ");",
                               cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }
    }

    private void btnAddEmployeeClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("INSERT INTO Personnel.Employees " +
                               "VALUES(N'" + txtEmployeeName.Text + 
                               "', N'" + dtpDateHired.Value + "');",
                               cntExercise);

            cntExercise.Open();
            SqlDataReader rdrEmployees = cmdExercise.ExecuteReader();
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Remember that you have many controls other than the calendar or the date picker that allow a user to provide a date value. If you use a control that does not impose date selection (like the calendar and the date picker do), you may have to validate the date or convert the value before passing it to the column of the table.

Text-based controls are the easiest objects to present values to the user.

We already know how to display values using the label, the text box, and the masked text box. In the same way, those controls can be used to display date values. Here are examples:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MaskedTextBoxDataSelection
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("SELECT ALL * FROM Personnel.Employees;",
                                    cntExercise);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("EmployeesSet");

                cntExercise.Open();

                sdaEmployees.SelectCommand = cmdEmployees;
                sdaEmployees.Fill(dsEmployees);

                mtbEmployeeNumber.Text = dsEmployees.Tables[0].Rows[0]["EmployeeNumber"].ToString();
                txtFirstName.Text = dsEmployees.Tables[0].Rows[0]["FirstName"].ToString();
                txtLastName.Text = dsEmployees.Tables[0].Rows[0]["LastName"].ToString();
                mtbDateHired.Text = DateTime.Parse(dsEmployees.Tables[0].Rows[0]["DateHired"].ToString()).ToShortDateString();
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

The calendar control is probably the most appropriate control to display the value of a date-based control:

• The advantage of the calendar control is that it accepts only valid date values
• The disadvantage of the calendar control is that it accepts only valid date values

Notice that both the advantage and the disadvantage are subject to the same rule. This means that, if the value you submit to the calendar is not valid, the control would produce an error. Otherwise, if you provide the right value, the calendar would display it. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MonthCalendarDataSelection
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("SELECT ALL * FROM Personnel.Employees;",
                                    cntExercise);
                SqlDataAdapter sdaEmployees = new SqlDataAdapter();
                DataSet dsEmployees = new DataSet("EmployeesSet");

                cntExercise.Open();

                sdaEmployees.SelectCommand = cmdEmployees;
                sdaEmployees.Fill(dsEmployees);

                mtbEmployeeNumber.Text = dsEmployees.Tables[0].Rows[0]["EmployeeNumber"].ToString();
                txtFirstName.Text = dsEmployees.Tables[0].Rows[0]["FirstName"].ToString();
                txtLastName.Text = dsEmployees.Tables[0].Rows[0]["LastName"].ToString();
                calDateHired.SelectionStart = DateTime.Parse(dsEmployees.Tables[0].Rows[0]["DateHired"].ToString());
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Of course, the calendar must be able to recognize those values. This means that you either must have taken care of that when creating the table or you can take appropriate actions when retrieving the values of the column.

Data Selection With the Date Picker

Besides the month calendar control, the date picker is another good candidate to display date values. It primarily follows the same rules as the month calendar.

When you sort records based on a column that uses the date data types, the SQL interpreter must be able to identify each date/time value. Fortunately, the database engine can validate each date value and reject those that are not valid.

As seen for the other data types, if you sort records based on a column that uses a date/time type and if the column has null values, the records with null values would show first. The values of a date/time field are sorted in chronological orders. Here is an example:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnSort;
    Button btnCreateTable;
    DataGridView dgvIceCream;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTable = new Button();
        btnCreateTable.Text = "Create Table";
        btnCreateTable.Location = new Point(12, 12);
        btnCreateTable.Width = 100;
        btnCreateTable.Click += new EventHandler(btnCreateTableClick);

        btnSort = new Button();
        btnSort.Text = "Sort";
        btnSort.Location = new Point(120, 12);
        btnSort.Click += new EventHandler(btnSortClick);

        dgvIceCream = new DataGridView();
        dgvIceCream.Location = new Point(12, 46);

        Text = "Ice Cream Factory";
        Controls.Add(btnCreateTable);
        Controls.Add(btnSort);
        Controls.Add(dgvIceCream);

        StartPosition = FormStartPosition.CenterScreen;
        dgvIceCream.Width = this.Width - 30;
        dgvIceCream.Height = this.Height - 80;
        dgvIceCream.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Integrated Security='SSPI';"))
        {
            SqlCommand cmdIceCream =
                    new SqlCommand("CREATE DATABASE IceCreamFactory;",
                        cntIceCream);

            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();

            MessageBox.Show("A database named IceCreamFactory has been created.",
                            "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Database='IceCreamFactory';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                new SqlCommand("CREATE SCHEMA IceCream;",
                                cntIceCream);

            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();

            MessageBox.Show("A schema named IceCream has been created",
                   "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Database='IceCreamFactory';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                new SqlCommand("CREATE TABLE IceCream.Orders1" +
                               "(" +
                               "    OrderID int identity(1, 1) not null, " +
                               "    OrderDate date not null, OrderTime time null " +
                               ") " +
                               "INSERT IceCream.Orders1(OrderDate, OrderTime) " +
                               "VALUES(N'2011-02-14', N'10:12'), (N'2011-02-15', N'09:08'), " +
                               "      (N'2011-05-10', N'15:24'), (N'2011-07-04', N'14:01'), " +
                               "      (N'2011-04-18', N'19:16'), (N'2011-04-18', N'09:15'), " +
                               "      (N'2011-04-18', N'12:48'), (N'2011-07-04', N'11:26')",
                                cntIceCream);

            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();

            MessageBox.Show("A table named Orders has been created in the IceCream schema",
                   "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Database='IceCreamFactory';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                new SqlCommand("SELECT OrderID, OrderDate FROM IceCream.Orders1 " +
                               "ORDER BY OrderDate;",
                                cntIceCream);
            SqlDataAdapter sdaIceCream = new SqlDataAdapter();
            DataSet dsIceCream = new DataSet("IceCreamSet");

            cntIceCream.Open();

            sdaIceCream.SelectCommand = cmdIceCream;
            sdaIceCream.Fill(dsIceCream);

            dgvIceCream.DataSource = dsIceCream.Tables[0];
        }
    }
}

public class Program
{
    [STAThread]
    static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce

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.

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))
GO

Like a string, the value of a date is provided or initialized in single-quotes. Unlike a string, the value of a date must follow strict rules inside the single-quotes, otherwise the value would be invalid. When performing comparisons on date-based fields, you must keep those rules in mind. Unlike strings but like numbers, date values support all Boolean operators.

Boolean operators used in date fields work as follows:

Here is an example:

SELECT StudentNumber, FirstName, LastName, DateOfBirth,
       Gender, City, SingleParentHome
FROM   Registration.Students
WHERE  DateOfBirth >= '1995-01-01';
GO

When it comes to comparisons, date values follow the same approach as numbers, but date values add some logical rules. To negate a date comparison, you can precede it with the NOT operator. If you negate an equality comparison, the expression would mean that the date doesn't occur on the indicated value, but it doesn't indicate whether the date occurs before or after that date. Therefore, in most cases, to negate a date comparison, you should use the opposite of the operator. Because date values support all Boolean operators, the opposites of those operators apply in the same logic we reviewed for numeric values:

As mentioned already, you have many options to use functions in your database. That is, Transact-SQL, the .NET Framework, and the Visual Basic library have many built-in functions you can use. You can also create your own functions in SQL or in a custom library (DLL).

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. To create a date in the .NET Framework, you can use the following constructor of the DateTime structure:

public DateTime(int year, int month, int day);

To let you get the current date 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

To get the current date from the .NET Framework, the DateTime structure provides a property named Today:

public static DateTime Today { get; }

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

To convert a string to a date value in the .NET Framework, the DateTime structure is equipped with the Parse() method that is overloaded with various versions.

Transact-SQL and the .NET Framework provide extensive support in the way a variable produces or a column displays the value of a date. In T-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.

To perform the same operation in the .NET Framework, the string data type is equipped with the Format() method that is overloaded with various versions. One of the versions uses the following syntax:

public static string Format(string format, Object value);

The first argument of this method is the same as the second argument of the Transact-SQL's FORMAT() function. The second argument is the same as the first argument of the FORMAT() function.

The days of months are numbered from 1 to 31, depending on the month. When way you can display the day is to use dd (lowercase). 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

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

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

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.

If you are passing an expression that should produce at least one more part such as the day, you can include M (uppercase). 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 display a month by its name using one of two formats: short (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec) or long name (January, February, March, April, May, June, July, August, September, October, November, or December).  To display the short name of a month, pass the format as MMM (uppercase).

The long names of months are . 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

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. 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. Whether passed by itself or in an expression that contains the other parts, yyy (lowercase) or yyyy (lowercase) produces a year in 4 digits.

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

Here is an example that produces a 4-digit year:

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

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

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.

To let you get the short date format, the DateTime structure is equipped with the ToShortDateString() method whose syntax is:

public string ToShortDateString();

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

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

To let you get the long date format, the DateTime structure is equipped with the ToLongDateString() method whose syntax is:

public string ToLongDateString();

Date addition consists of adding a number of days, a number of months, or a number of years, to a date value. The Transact-SQL 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

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

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

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

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:

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

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.

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 value. To let you get the day of a date value, the DateTime structure is equipped with the Day property:

public int Day { get; }

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

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

To let you get the month of a date value, the DateTime structure is equipped with the Month property:

public int Month { get; }

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

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. To let you get the year of a date value, the DateTime structure is equipped with the Year property:

public int Year { get; }

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

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

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