SQL Server Tutorial: Variables for datetime data

Показать описание

---

Now that you know how to do EDA on SQL DateTime data, let's learn how to create and store DateTime data in variables.

When we are writing functions and stored procedures, we usually need to temporarily store data. One of the ways we can do this is with variables. Think of a variable as a locker: the variable name is the label on the locker, the size of the locker depends on the datatype used, and the value is the contents.

Here we are creating four different variables. The one on the left is @StartDateTime and is a DateTime type which requires 8 bytes of storage. The two in the middle are time and date datatypes which are only 5 and 3 bytes respectively. When we declare @StartTime we also assign the initial value to 8AM. 8AM is the contents of the locker and can be changed.

We can even create a table variable as shown on the right. Here we define each column with a name and datatype just like we do with a single variable. Remember that all SQL variable names must include an at symbol as the first character of the name. For table variables, this only applies to the table variable name, but not its columns.

Let's inspect the DECLARE statements from the previous slide. We can assign the initial value of @StartTime when we declare it, but what if we don't know the value we want to assign at that point?

SET can be used to assign a value to a variable after it has been declared.

You can even set the value to the result of a select statement as we're doing with @BeginDate in the last code section.

How can we combine a date and time variable into one DateTime variable? We can use the CAST() function. CAST() is very similar to the CONVERT() function you learned previously, although its the ANSI standard and works across SQL platforms. CAST() converts an expression to a different datatype. Think about CAST() as transferring one locker's contents to a different size locker.

The add operator can be used with CAST() to combine two DateTime variables, but first, we have to CAST() @StartTime from a time to a DateTime, as well as @BeginDate from a date to a DateTime. SQL uses default value rules when executing CAST() statements so you can CAST() a time to a DateTime even when there is no date value present. When a time variable is cast from a time locker to a DateTime locker, SQL will make it a valid DateTime and include a default date value.

Now let's dig into table variables. They can be used to store a set of rows, which is often a result of a query. Here we are creating a table variable named @TaxiRideDates and it has two columns; StartDate and EndDate, which are both date datatypes.

Just like scalar or single variables we can assign static values to a table variable.

We can also assign values from a select query like this. Since PickupDate and DropOffDate are both DateTime datatypes in the YellowTripTaxi table, we use CAST to change them to a date. Now they match the datatype specified in the DECLARE or the type of locker. The DISTINCT keyword is used to limit the query results to only unique PickupDate and DropOffDate values.

Keep in mind that table variables are not recommended to store more than 100 records because of potential performance impact.

You've got this. Now give it a go!