Time Values
Time Values
Introduction to Time Values
Overview
A time is a non-spatial measure used to count a certain number of lapses that have occurred from a non-spatial starting point. The primary starting point is called midnight. That is, it is positioned in the middle of the night.
The primary unit of measure of time is called the second. A second is identified by an integer. A second itself divided in 1000 fractions, counted from 0 to 999 and called milliseconds. A millisecond is identified by an integer. A measure of 60 consecutive seconds, counted from 0 to 59, is called a minute. A minute is identified by an integer. A group of 60 minutes, counted from 0 to 59, is called an hour. An hour is identified by an integer. A group of 24 consecutive hours is called a day.
The rules to represent a time value are specified in the Time tab of the Customize Regional Options of the Regional and Language Options of the Control Panel:
We continue with our University5 database. Before the beginning of a semester, the school must publish a catalog. The catalog has a list of schedules for each course. The students must consult that catalog to enroll in a course. The catalog specifies:
|
Practical Learning: Introducing Time Values
Time Values |
To support time values, Transact-SQL provides a data type named TIME. You can use it to declare a time-based variable or you can specify that type for a column. To initialize a TIME-based variable, provide its value as a string that use the following formula:
hh:mm hh:mm:ss hh:mm:ss[.fractional seconds]
In Transact-SQL, a time-value is created as a string in single-quotes. To indicate that you want to follow Unicode rules, precede the value with N.
The first part represents the hour with a value between 1 and 23. Any value outside of this range will cause an error. If the value is less than 10, you can optionally write it with a leading 0, as in 08. The second part represents the minutes and holds a value between 1 and 59; otherwise, an error would be thrown. If the value is less than 10, you can type it with a leading 0, as in 04. The values are separated by :. An example is 10:25.
Here is an example of declaring and initializing a time-based variable:
1> DECLARE @ArrivalTime time; 2> SET @ArrivalTime = N'18:22'; 3> SELECT @ArrivalTime AS [Arrival Time]; 4> GO Arrival Time ---------------- 18:22:00.0000000 (1 rows affected) 1>
The third part of our formula is optional and represents the seconds portion of the time. It holds a value between 1 and 59. If the value is less than 10, you can provide it with a leading 0. This part is separated from the previous one with :. This means that you can also initialize a time value using the hh:mm:ss formula as a string. Here is an example:
DECLARE @TimeValue TIME; SET @TimeValue = N'20:22:52'; SELECT @TimeValue; GO
This would produce:
The last part also is optional. It allows you to provide the milliseconds part of the time. If you want to provide it, enter a value between 1 and 999. This is separated from the seconds part with a period ".".
A time value can be represented in standard or military format. In standard format, you must indicate whether the time occurs before noon or after noon. In both cases, the hour part is given between 0 and 11. If the time occurs before noon, the value ends with AM. An example is 08:12 AM. If the time occurs after noon, the value ends with PM. An example is 04:35 PM.
In military time, the hour part is given between 00 and 23. It includes neither AM nor PM. Examples are 08:12 or 15:18. If the hour portion is between 12 and 23, the time is set in the afternoon. This means that you can also initialize a time value by specifying the AM/PM portion as follows. Here is an example:
DECLARE @TimeValue TIME; SET @TimeValue = N'10:22:52 PM'; SELECT @TimeValue; GO
This would produce:
In the absence of an AM/PM section, the database interpreter would evaluate the hour portion to find out whether the time occurs in the morning or in the afternoon. The AM and the PM can be in uppercase or lowercase. In other words the AM/PM portion can be represented as AM, Am, aM, am, PM, Pm, pM, or pm. Only the characters A and P (uppercase or lowercase) are accepted as the first character. Only the M or m characters are accepted as the second character. Any other combination or other character will cause an error.
Time-Based Columns |
To create a column that would store time values, select or apply TIME as the data type:
To specify the time value of a TIME-based column, you use the same formulas we saw for the variables:
hh:mm hh:mm:ss hh:mm:ss[.fractional seconds]
Practical Learning: Creating Time Values |
Column Name | Data Type |
CourseCode | nvarchar(10) |
Semester | nvarchar(20) |
StartTime | time(7) |
EndTime | time(7) |
Weekdays | nvarchar(20) |
Course Code | Semester | StartTime | EndTime | Weekdays |
WRTG 101 | SPRING 2012 | 08:15 | 10:10 | M-W |
WRTG 245 | SPRING 2012 | 10:25 | 12:20 | M-W |
CHEM 101 | SUMMER 2012 | 13:25 | 15:20 | M-W |
WRTG 215 | SUMMER 2012 | 13:25 | 15:20 | M-W |
CMIS 210 | SUMMER 2012 | 08:20 | 17:00 | S |
USE University5; GO INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays) VALUES(N'MATH 106', N'SPRING 2012', N'08:15', N'10:10', N'M-W'); GO INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays) VALUES(N'MATH 120', N'SPRING 2012', N'08:15 AM', N'10:10 AM', N'M-W'); GO INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays) VALUES(N'SOCY 100', N'SUMMER 2012', N'15:35', N'17:20', N'T-H'); GO INSERT INTO Academics.UndergraduateSchedules(CourseCode, Semester, StartTime, EndTime,Weekdays) VALUES(N'GVPS 140', N'SUMMER 2012', N'3:35 PM', N'5:20 PM', N'T-H'); GO
Data Analysis On Time Values |
Introduction |
When you sort records based on a column that uses a time-based column, the SQL interpreter must be able to identify each time value. Fortunately, the database engine will have validated each time value and reject those that were not valid.
If you sort records based on a column that uses a time type and if the column has null fields, the records with null would show up first. The values are sorted in their order of occurrence. If the table has only time values, the database engine would (or may) consider that all time values occur on the same day. In that case (or if that's the case), if you sort the records on the column that has the time values, the values closer to 0:01 AM would first appear, then the values that occur closer to 23:59 (midnight) on the same day would appear last.
Practical Learning: Sorting Time-Based Fields |
USE University5; GO SELECT Semester, CourseCode, StartTime, EndTime, Weekdays FROM Academics.UndergraduateSchedules ORDER BY StartTime; GO
Time Values and Logical Comparisons |
Remember that Transact-SQL supports all regular logical operators:
Practical Learning: Filtering Time-Based Fields |
SELECT Semester, CourseCode, StartTime, EndTime, Weekdays FROM Academics.UndergraduateSchedules WHERE StartTime = N'01:25 PM'; GO
Time Values and Functions |
Creating a Time From Parts |
As mentioned already, a time value is made of an hour value, a minute value, and the seconds. We already saw how to supply those values as a string. As an alternative, to let you create a time value if you have those parts, Transact-SQL provides a function named TIMEFROMPARTS. Its syntax is:
TIMEFROMPARTS(int hour, int minute, int seconds, int fractions, int precision) RETURNS time;
The hour value must be between 0 and 23. The minute must be between 0 and 59. The second argument must have a value between 0 and 59. Whenever the seconds are not important to represent the time, provide their value as 0. Here is an example:
DECLARE @TimeValue time; SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 0, 0); SELECT @TimeValue; GO
This would produce:
A time value is subject to precision, that is, how accuracte the value should be. That's the role of the last two arguments of the TIMEFROMPARTS() function. These two arguments should be provided together because one controls the other:
DECLARE @TimeValue time; SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 99, 2); SELECT @TimeValue [Time Value]; GOThis would produce:
DECLARE @TimeValue time; SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 999, 2); SELECT @TimeValue [Time Value]; GOThis would produce:
DECLARE @TimeValue time; SET @TimeValue = TIMEFROMPARTS(8, 20, 42, 9999999, 7); SELECT @TimeValue [Time Value]; GOThis would produce:
The fraction argument must not have a value other than those ones; otherwise you would receive an error.
Practical Learning: Creating Time Values |
INSERT INTO Academics.UndergraduateSchedules(Semester, CourseCode,
StartTime, EndTime,Weekdays)
VALUES(N'FALL 2012', N'CMIS 210', TIMEFROMPARTS(8, 20, 0, 0, 0),
TIMEFROMPARTS(17, 0, 0, 0, 0), N'M-T-W-H-F');;
GO
SELECT Semester,
CourseCode,
StartTime,
EndTime,
Weekdays
FROM Academics.UndergraduateSchedules;
GO
The Current Time |
To let you get the current (date and) time of the computer, Transact-SQL provides various functions. One of them is named GETDATE. Its syntax is:
GETDATE();
This function simply returns the (date and) time of the computer where the function is called. As mentioned already, time is subject to precision. For that matter, Transact-SQL provides a function named SYSDATETIME. Its syntax is:
SYSDATETIME();
Converting a String to Time |
As mentioned previously, to let you convert a value from one type to another, such as to convert a a string to a time, Transact-SQL provides the TRY_PARSE() and TRY_CONVERT() functions. These functions take a string as argument and they scan that argument. If the value is not a valid time, the functions returns NULL. Here is an example:
DECLARE @StrValue nvarchar(20), @TimeValue time; SET @StrValue = N'1008'; SET @TimeValue = TRY_CONVERT(time, @StrValue); SELECT @StrValue; SELECT @TimeValue; GO
This would produce:
If the value is a valid (date or) time, the function returns it. Here is an example:
DECLARE @StrValue nvarchar(20), @TimeValue time; SET @StrValue = N'10:08'; SET @TimeValue = TRY_CONVERT(time, @StrValue); SELECT @StrValue; SELECT @TimeValue; GO
This would produce:
Formatting a Time Value |
Even though there is usually a standard way to display time, Transact-SQL provides a function to control how a time value should be presented. The function to do this is named FORMAT and its syntax is:
FORMAT(value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar
This function takes two required arguments. The third argument is optional. The first argument is the original time value. The second argument specifies how the formatting must be carried. The computer has a default way to display time. In US English, the default time is from 00:00 to 23:59. To present a time value in the default format, pass the second value as c (in lowercase). Here is an example:
DECLARE @StrValue nvarchar(20), @TimeValue time; SET @TimeValue = N'09:24 PM'; SET @StrValue = FORMAT(@TimeValue, N'c'); SELECT @StrValue; SELECT @TimeValue; GO
This would produce:
Time Addition |
One of the primary operations you may want to perform on a time value would consist of adding a number to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:
DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)
The third argument to this function is the value of a time on which the operation will be performed. It can be a constant value that uses a valid format. The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.
When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:
If the TypeOfValue is | As a result | ||
Hour | hh | A number of hours will be added to the time value | |
minute | n | mi | A number of minutes will be added to the time value |
second | s | ss | A number of seconds will be added to the time value |
millisecond | ms | A number of milliseconds will be added to the time value |
Here is an example that adds two hours to a time value:
DECLARE @TimeValue time,
@Result time;
SET @TimeValue = N'23:38';
SET @Result = DATEADD(hh, 2, @TimeValue);
SELECT @TimeValue;
SELECT @Result;
GO
This would produce:
Time Subtraction |
Another regular operation performed on a time value consists of getting the number of units that has elapsed in the range of two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:
DATEDIFF(TypeOfValue, StartDate, EndDate)
This function takes three arguments. The second argument is the starting time of the range to be considered. The third argument is the end or last time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function. Here is an example:
DECLARE @Start time,
@End time,
@Result int;
SET @Start = N'12:24';
SET @End = N'15:06';
SET @Result = DATEDIFF(n, @Start, @End);
SELECT @Start [Start Time];
SELECT @End [End Time];
SELECT @Result;
GO
This would produce:
The Part Name of a Time Value |
Sometimes you want to get a component of a time value, such as the hour, the minute, or the second. To assist you with this, Transact-SQL is equipped with a function named DATENAME. Its syntax is:
DATENAME(integer ReturnedValue, date Value) RETURNS nvarchar;
The first argument specifies the value you want to get from the function. That argument can be one of the following:
If the ReturnedValue is | The function will return | ||
Hour | hh | The hour part | |
minute | n | mi | The minute part |
second | s | ss | The second |
millisecond | ms | The millisecond | |
microsecond | mcs | The microsecond | |
nanosecond | ns | The nanosecond |
The second argument is the time that holds the value from which the time will be produced. Here is an example:
DECLARE @TimeValue time,
@Result int;
SET @TimeValue = N'23:38';
SET @Result = DATENAME(hh, @TimeValue);
SELECT @TimeValue;
SELECT @Result;
GO
Besides DATENAME(), Transact-SQL also provides a function named DATEPART. Its syntax is:
int DATEPART (integer datepart, date Value)
This function takes the exact same arguments as DATENAME and both functions essentially behave the same. The main difference is that DATENAME() returns a string while DATEPART() returns an integer. Remember that what you are interested in is the value of the first argument. This means that these two functions perform the same operation.
Using Date and Time Values
Instead of using only the time or only a date value, you may want to use theic combinaion. You have two options. You can use two separate variables or columns, or you can combine both values in one unit.
We already know how to declare a date variable, how to create a date-based column, how to declare a time variable, and how to create a time-based column. In the same way, you can create a table that uses one or many combinations of date-based columns and time-based columns. After creating a column, you can add the records as necessary.
Data analysis on date-based columns and time-based columns follows the descriptions with which we are already familiar.
If you sort records on a table that uses combinations of date and time columns:
Practical Learning: Using a Combination of Date/Time Columns |
CREATE TABLE Academics.UndergraduateSchedules ( Semester nvarchar(20), StartDate date, EndDate date, StartTime time, EndTime time, Weekdays nvarchar(32), CourseCode nvarchar(10) ); GO INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'08:15', N'10:10', N'M-W', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'M-W', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'08:15', N'10:10', N'M-W-F', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120226', N'10:25', N'12:20', N'M-W-F', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'10:25', N'12:20', N'M-W', N'MATH 106'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'08:15', N'10:10', N'T-H', N'MATH 106'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'13:25', N'15:30', N'T-H', N'EDPD 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'T-H', N'EDPD 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'13:25', N'15:30', N'M-W', N'EDPD 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SPRING 2012', N'20120109', N'20120318', N'15:35', N'17:30', N'M-W', N'EDPD 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'WRTG 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'M-W', N'EDPD 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'MATH 106'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'13:25', N'15:20', N'M-W', N'MATH 115'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'HIST 140'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'T-H', N'HIST 140'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'08:15', N'10:10', N'M-W', N'SOCY 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'10:25', N'12:20', N'M-W', N'SOCY 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'08:15', N'10:10', N'T-H', N'SOCY 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120708', N'10:25', N'12:20', N'T-H', N'SOCY 100'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'15:35', N'17:30', N'T-H', N'HIST 215'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'13:25', N'15:20', N'T-H', N'HIST 215'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'T-H', N'FINA 101'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'M-W-F', N'GVPS 140'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'10:25', N'12:20', N'T-H', N'HIST 216'); INSERT INTO Academics.UndergraduateSchedules(Semester, StartDate, EndDate, StartTime, EndTime, Weekdays, CourseCode) VALUES(N'SUMMER 2012', N'20120514', N'20120819', N'08:15', N'10:10', N'M-W', N'ACCT 311'); GO
SELECT Semester, StartDate "Start Date", EndDate "End Date", StartTime "Start Time", EndTime "End Time", Weekdays, CourseCode "Course Code" FROM Academics.UndergraduateSchedules ORDER BY StartDate; GO
SELECT Semester, StartDate "Start Date", EndDate "End Date", StartTime "Start Time", EndTime "End Time", Weekdays, CourseCode "Course Code" FROM Academics.UndergraduateSchedules ORDER BY StartTime; GO
SELECT Semester, StartDate "Start Date", EndDate "End Date", StartTime "Start Time", EndTime "End Time", Weekdays, CourseCode "Course Code" FROM Academics.UndergraduateSchedules ORDER BY StartDate, StartTime; GO
SELECT Semester, StartTime "Start Time", EndTime "End Time", StartDate "Start Date", EndDate "End Date", Weekdays, CourseCode "Course Code" FROM Academics.UndergraduateSchedules ORDER BY StartDate, StartTime; GO
So far, we have used date values and time values in separate variables and columns. Transact-SQL gives you the ability to combine a date and time values into one unit. To support this, it provides the DATETIME2 data type. This data type counts dates from January 1st, 0001 and ends on December 31st, 9999. Probably the biggest feature of this data type is that it is a combination of the date and the time data types. This means that everything we have seen about both types is available here.
To declare a variable that can combine date and time values, use the DATETIME2 data type. In the same way, you can create a column of a table and apply this data type to it (the column). To initialize the variable or assign a value to the column, you can use any of the formulas we saw for dates or for times, or combine those formulas. This means that you can use one of the following formulas:
YYYYMMDD YYYYMMDD hh:mm:ss YYYYMMDD hh:mm:ss[.fractional seconds] YYYY-MM-DD YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss[.fractional seconds] MM-DD-YY MM-DD-YY hh:mm:ss MM-DD-YY hh:mm:ss[.fractional seconds] MM-DD-YYYY MM-DD-YYYY hh:mm:ss MM-DD-YYYY hh:mm:ss[.fractional seconds] MM/DD/YY MM/DD/YY hh:mm:ss MM/DD/YY hh:mm:ss[.fractional seconds] MM/DD/YYYY MM/DD/YYYY hh:mm:ss MM/DD/YYYY hh:mm:ss[.fractional seconds]
Remember to include the value in single-quotes. Other than that, all the ways we saw to specify the value of a date or of a time can be used with the DATETIME2 data type. As mentioned already, you can also combine the values. Here are examples:
DECLARE @FullName nvarchar(60), @DateOfBirth date, @DateRegistered datetime2 SET @FullName = N'John Summons'; SET @DateOfBirth = N'19960426'; SET @DateRegistered = N'20090629'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'James Haans'; SET @DateOfBirth = N'1994-10-25'; SET @DateRegistered = N'2009-08-02'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'Gertrude Monay'; SET @DateOfBirth = N'06-16-92'; SET @DateRegistered = N'2009-12-24 12:36'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'Philomène Guillon'; SET @DateOfBirth = N'1996-10-16'; SET @DateRegistered = N'10/14/08 09:42:05.136'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'Eddie Monsoon'; SET @DateOfBirth = N'08/10/96'; SET @DateRegistered = N'2009-06-02 12:36'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'Peter Mukoko'; SET @DateOfBirth = N'03-10-1994'; SET @DateRegistered = N'7/22/2009 10:24:46.248'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; SET @FullName = N'Chritian Allen'; SET @DateOfBirth = N'06/16/1995'; SET @DateRegistered = N'02-09-2009 12:36'; SELECT @FullName AS [Full Name], @DateOfBirth AS [Date of Birth], @DateRegistered AS [Date Registered]; GO
Besides the DATE, the TIME, and the DATETIME2 data types, Transact-SQL supports the smalldatetime and the datetime data types. These are old data types. Although still available, they are kept for backward compatibility and you should avoid using them.
A Date/Time From Parts |
One of the ways you can create a value that is a combination of date and time is by using a function named DATETIME2FROMPARTS. Its syntax is:
DATETIME2FROMPARTS(int year, int month, int day, int hour, int minute, int seconds, int fractions, int precision) RETURNS datetime2;
As you can see, this function takes 8 required arguments. As mentioned already, the datetime2 is a combination of date and time. Based on this, the first 3 arguments are the same used in the DATEFROMPARTS() function. The other arguments are the same used in the TIMEFROMPARTS() Function.
Data Analysis On Date/Time Values |
Sorting Date and Time-Based Fields |
If you sort records based on a column that uses a combination of a date and a time values, if the column has null fields, the records with null would display first. If the values are combinations of date and time values, the date values would first be sorted in chronological order. If some records occur on the same day but at different times, the records with similar dates would be grouped first. Then inside a group, the records woulds be sorted by time. Consider the following example:
USE master; GO CREATE DATABASE IceCreamFactory1; GO USE IceCreamFactory1; GO CREATE SCHEMA IceCream; GO CREATE TABLE IceCream.Orders ( OrderID int identity(1, 1) not null, OrderedPeriod DateTime2 null ); GO INSERT IceCream.Orders(OrderedPeriod) VALUES(N'2012-02-14 10:12'), (N'2012-02-15 09:08'), (N'2012-05-10 15:24'), (N'2012-07-04 14:01'), (N'2012-04-18 19:16'), (N'2012-04-18 09:15'), (N'2012-04-18 12:48'), (N'2012-07-04 11:26'); GO SELECT OrderID, OrderedPeriod FROM IceCream.Orders ORDER BY OrderedPeriod; GO
This would produce
Logical Comparisons on Date/Time Fields |
You can perform all types of logical comparisons on variables or columns that use the datetime2 data type. The value may have a date only, a time only, or a combination of date and time. If the value has only a date, the comparison follows the description we saw for the date data type. If the value has only a time, the comparison follows the rules for the time data type. If the value uses a combination of date and time:
USE master; GO DECLARE @FirstOrder datetime2, @SecondOrder datetime2; SET @FirstOrder = N'2012-02-14 10:12:00'; SET @SecondOrder = N'2012-02-14 10:12:00'; IF @FirstOrder = @SecondOrder PRINT N'Both orders were made at the same time.'; ELSE PRINT N'The orders were made at different times.'; GOThis would produce
If there is a slight difference in the value, even for one second, the equality comparison produces a false value:
USE master; GO DECLARE @FirstOrder datetime2, @SecondOrder datetime2; SET @FirstOrder = N'2012-02-14 10:12:00'; SET @SecondOrder = N'2012-02-14 10:12:01'; IF @FirstOrder < @SecondOrder PRINT N'The first order was placed before the second.'; ELSE PRINT N'Either both orders were made at the same time or the first was after the second.'; GO
Practical Learning: Ending the Lesson |
|
||
Previous | Copyright © 2008-2022, FunctionX, Inc. | Next |
|