Single-Table Queries in Microsoft SQL Server 2012
- 7/15/2012
Working with Date and Time Data
Working with date and time data in SQL Server is not trivial. You will face several challenges in this area, such as expressing literals in a language-neutral manner and working with date and time separately.
In this section, I first introduce the date and time data types supported by SQL Server; then I explain the recommended way to work with those types; and finally I cover date-related and time-related functions.
Date and Time Data Types
Prior to SQL Server 2008, SQL Server supported two date and time data types called DATETIME and SMALLDATETIME. Both types include date and time components that are inseparable. The two data types differ in their storage requirements, their supported date range, and their accuracy. SQL Server 2008 introduced separate DATE and TIME data types, as well as DATETIME2, which has a bigger date range and better accuracy than DATETIME; and DATETIMEOFFSET, which also has a time zone offset component. Table 2-1 lists details about date and time data types, including storage requirements, supported date range, accuracy, and recommended entry format.
Table 2-1. Date and Time Data Types
Data Type |
Storage (bytes) |
Date Range |
Accuracy |
Recommended Entry Format and Example |
DATETIME |
8 |
January 1, 1753, through December 31, 9999 |
3 1/3 milliseconds |
‘YYYYMMDD hh:mm:ss.nnn’ ‘20090212 12:30:15.123’ |
SMALLDATETIME |
4 |
January 1, 1900, through June 6, 2079 |
1 minute |
‘‘YYYYMMDD hh:mm’ ‘20090212 12:30’ |
DATE |
3 |
January 1, 0001, through December 31, 9999 |
1 day |
‘YYYY-MM-DD’ ‘2009-02-12’ |
TIME |
3 to 5 |
N/A |
100 nanoseconds |
‘hh:mm:ss.nnnnnnn’ ‘12:30:15.1234567’ |
DATETIME2 |
6 to 8 |
January 1, 0001, through December 31, 9999 |
100 nanoseconds |
‘YYYY-MM-DD hh:mm:ss.nnnnnnn’ ‘2009-02-12 12:30:15.1234567’ |
DATETIMEOFFSET |
8 to 10 |
January 1, 0001, through December 31, 9999 |
100 nanoseconds |
‘YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm’ ‘2009-02-12 12:30:15.1234567 +02:00’ |
The storage requirements for the last three data types in Table 2-1 (TIME, DATETIME2, and DATETIMEOFFSET) depend on the precision you choose. You specify the precision as an integer in the range 0 to 7 representing the fractional-second precision. For example, TIME(0) means 0 fractional-second precision—in other words, one-second precision. TIME(3) means one-millisecond precision, and TIME(7) means 100-nanosecond accuracy. If you don’t specify a fractional-second precision, SQL Server assumes 7 by default with all three aforementioned types.
Literals
When you need to specify a literal (constant) of a date and time data type, you should consider several things. First, though it might sound a bit strange, SQL Server doesn’t provide the means to express a date and time literal; instead, it allows you to specify a literal of a different type that can be converted—explicitly or implicitly—to a date and time data type. It is a best practice to use character strings to express date and time values, as shown in the following example.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate = '20070212';
SQL Server recognizes the literal ‘20070212’ as a character string literal and not as a date and time literal, but because the expression involves operands of two different types, one operand needs to be implicitly converted to the other’s type. Normally, implicit conversion between types is based on what’s called data type precedence. SQL Server defines precedence among data types and will usually implicitly convert the operand that has a lower data type precedence to the one that has higher precedence. In this example, the character string literal is converted to the column’s data type (DATETIME) because character strings are considered lower in terms of data type precedence with respect to date and time data types. Implicit conversion rules are not always that simple, and in fact different rules are applied with filters and in other expressions, but for the purposes of this discussion, I’ll keep things simple. For the complete description of data type precedence, see “Data Type Precedence” in SQL Server Books Online.
The point I’m trying to make is that in the preceding example, implicit conversion takes place behind the scenes. This query is logically equivalent to the following one, which explicitly converts the character string to a DATETIME data type.
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = CAST('20070212' AS DATETIME);
It is important to note that some character string formats of date and time literals are language dependent, meaning that when you convert them to a date and time data type, SQL Server might interpret the value differently based on the language setting in effect in the session. Each logon defined by the database administrator has a default language associated with it, and unless it is changed explicitly, that language becomes the effective language in the session. You can overwrite the default language in your session by using the SET LANGUAGE command, but this is generally not recommended because some aspects of the code might rely on the user’s default language.
The effective language in the session sets several language-related settings behind the scenes, among them one called DATEFORMAT, which determines how SQL Server interprets the literals you enter when they are converted from a character string type to a date and time type. The DATEFORMAT setting is expressed as a combination of the characters d, m, and y. For example, the us_english language setting sets the DATEFORMAT to mdy, whereas the British language setting sets the DATEFORMAT to dmy. You can override the DATEFORMAT setting in your session by using the SET DATEFORMAT command, but as mentioned earlier, changing language-related settings is generally not recommended.
Consider, for example, the literal ‘02/12/2007’. SQL Server can interpret the date as either February 12, 2007 or December 2, 2007 when you convert this literal to one of the following types: DATETIME, DATE, DATETIME2, or DATETIMEOFFSET. The effective LANGUAGE/DATEFORMAT setting is the determining factor. To demonstrate different interpretations of the same character string literal, run the following code.
SET LANGUAGE British;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('02/12/2007' AS DATETIME);
Notice in the output that the literal was interpreted differently in the two different language environments.
Changed language setting to British. ----------------------- 2007-12-02 00:00:00.000 Changed language setting to us_english. ----------------------- 2007-02-12 00:00:00.000
Note that the LANGUAGE/DATEFORMAT setting only affects the way the values you enter are interpreted; these settings have no impact on the format used in the output for presentation purposes, which is determined by the database interface used by the client tool (such as ODBC) and not by the LANGUAGE/DATEFORMAT setting. For example, OLEDB and ODBC present DATETIME values in the format ‘ YYYY-MM-DD hh:mm:ss.nnn’.
Because the code you write might end up being used by international users with different language settings for their logons, understanding that some formats of literals are language dependent is crucial. It is strongly recommended that you phrase your literals in a language-neutral manner. Language-neutral formats are always interpreted by SQL Server the same way and are not affected by language-related settings. Table 2-2 provides literal formats that are considered neutral for each of the date and time types.
Table 2-2. Language-Neutral Date and Time Data Type Formats
Data Type |
Accuracy |
Recommended Entry Format and Example |
DATETIME |
‘YYYYMMDD hh:mm:ss.nnn’ ‘YYYY-MM-DDThh:mm:ss.nnn’ ‘YYYYMMDD’ |
‘20090212 12:30:15.123’ ‘2009-02-12T12:30:15.123’ ‘20090212’ |
SMALLDATETIME |
‘YYYYMMDD hh:mm’ ‘YYYY-MM-DDThh:mm’ ‘YYYYMMDD’ |
‘20090212 12:30’ ‘2009-02-12T12:30’ ‘20090212’ |
DATE |
‘YYYYMMDD’ ‘YYYY-MM-DD’ |
‘20090212’ ‘2009-02-12’ |
DATETIME2 |
‘YYYYMMDD hh:mm:ss.nnnnnnn’ ‘YYYY-MM-DD hh:mm:ss.nnnnnnn’ ‘YYYY-MM-DDThh:mm:ss.nnnnnnn’ ‘YYYYMMDD’ ‘YYYY-MM-DD’ |
‘20090212 12:30:15.1234567’ ‘2009-02-12 12:30:15.1234567’ ‘2009-02-12T12:30:15.1234567’ ‘20090212’ ‘2009-02-12’ |
DATETIMEOFFSET |
‘YYYYMMDD hh:mm:ss.nnnnnnn [+|-]hh:mm’ ‘YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm’ ‘YYYYMMDD’ ‘YYYY-MM-DD’ |
‘20090212 12:30:15.1234567 +02:00’ ‘2009-02-12 12:30:15.1234567 +02:00’ ‘20090212’ ‘2009-02-12’ |
TIME |
‘hh:mm:ss.nnnnnnn’ |
‘12:30:15.1234567’ |
Note a couple of things about Table 2-2. With all types that include both date and time components, if you don’t specify a time part in your literal, SQL Server assumes midnight. If you don’t specify a time-zone offset, SQL Server assumes 00:00. It is also important to note that the formats ‘YYYY-MM-DD’ and ‘YYYY-MM-DD hh:mm…’ are language dependent when converted to DATETIME or SMALLDATETIME, and language neutral when converted to DATE, DATETIME2 and DATETIMEOFFSET.
For example, notice in the following code that the language setting has no impact on how a literal expressed with the format ‘YYYYMMDD’ is interpreted when it is converted to DATETIME.
SET LANGUAGE British;
SELECT CAST('20070212' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('20070212' AS DATETIME);
The output shows that the literal was interpreted in both cases as February 12, 2007.
Changed language setting to British. ----------------------- 2007-02-12 00:00:00.000 Changed language setting to us_english. ----------------------- 2007-02-12 00:00:00.000
I probably can’t emphasize enough that using language-neutral formats such as ‘YYYYMMDD’ is a best practice, because such formats are interpreted the same way regardless of the LANGUAGE/DATEFORMAT settings.
If you insist on using a language-dependent format to express literals, there are two options available to you. One is by using the CONVERT function to explicitly convert the character string literal to the requested data type, in the third argument specifying a number representing the style you used. SQL Server Books Online has a table with all of the style numbers and the formats they represent, in “The CAST and CONVERT Functions.” For example, if you want to specify the literal ‘02/12/2007’ with the format mm/dd/yyyy, use style number 101, as shown here.
SELECT CONVERT(DATETIME, '02/12/2007', 101);
The literal is interpreted as February 12, 2007 regardless of the language setting that is in effect.
If you want to use the format dd/mm/yyyy, use style number 103.
SELECT CONVERT(DATETIME, '02/12/2007', 103);
This time, the literal is interpreted as December 2, 2007.
Another option is to use the PARSE function, which is available in SQL Server 2012. This function allows you to parse a value as a requested type and indicate the culture. For example, the following is the equivalent of using CONVERT with style 101 (US English).
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US');
The following is the equivalent to using CONVERT with style 103 (British English):
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');
Working with Date and Time Separately
SQL Server 2008 introduced separate DATE and TIME data types, but in previous versions there is no separation between the two components. If you want to work only with dates or only with times in versions of SQL Server prior to SQL Server 2008, you can use either DATETIME or SMALLDATETIME, which contain both components. You can also use types such as integers or character strings on which you implement the date and time logic, but I won’t discuss this option here. If you want to use the DATETIME or SMALLDATETIME type, when you want to work only with dates, you store the date with a value of midnight (all zeros in the time parts). When you want to work only with times, you store the time with the base date January 1, 1900.
For example, the orderdate column in the Sales.Orders table is of a DATETIME data type, but because only the date component is actually relevant, all values were stored at midnight. When you need to filter only orders from a certain date, you don’t have to use a range filter. Instead, you can use the equality operator like this.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate = '20070212';
When the character string literal is converted to DATETIME, SQL Server assumes midnight as the time component if time is not specified. Because all values in the orderdate column were stored with midnight in the time component, all orders placed on the requested date will be returned. Note that you can use a CHECK constraint to ensure that only midnight is used as the time part.
If the time component is stored with non-midnight values, you can use a range filter like this.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate >= '20070212' AND orderdate < '20070213';
If you want to work only with times in versions prior to SQL Server 2008, you can store all values with the base date of January 1, 1900. When SQL Server converts a character string literal that contains only a time component to DATETIME or SMALLDATETIME, SQL Server assumes that the date is the base date. For example, run the following code.
SELECT CAST('12:30:15.123' AS DATETIME);
You get the following output.
----------------------- 1900-01-01 12:30:15.123
Suppose you have a table with a column called tm of a DATETIME data type and you store all values by using the base date. Again, this could be enforced with a CHECK constraint. To return all rows for which the time value is 12:30:15.123, you use the filter WHERE tm = ‘12:30:15.123’. Because you did not specify a date component, SQL Server assumes that the date is the base date when it implicitly converts the character string to a DATETIME data type.
If you want to work only with dates or only with times, but the input values you get include both date and time components, you need to apply some manipulation on the input values to “zero” the irrelevant part. That is, set the time component to midnight if you want to work only with dates, and set the date component to the base date if you want to work only with times. I’ll explain how you can achieve this shortly, in the “Date and Time Functions” section.
Filtering Date Ranges
When you need to filter a range of dates, such as a whole year or a whole month, it seems natural to use functions such as YEAR and MONTH. For example, the following query returns all orders placed in the year 2007.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE YEAR(orderdate) = 2007;
However, you should be aware that in most cases, when you apply manipulation on the filtered column, SQL Server cannot use an index in an efficient manner. This is probably hard to understand without some background about indexes and performance, which are outside the scope of this book, but for now, just keep this general point in mind: To have the potential to use an index efficiently, you need to revise the predicate so that there is no manipulation on the filtered column, like this.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate >= '20070101' AND orderdate < '20080101';
Similarly, instead of using functions to filter orders placed in a particular month, like this:
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
use a range filter, like the following.
SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate >= '20070201' AND orderdate < '20070301';
Date and Time Functions
In this section, I describe functions that operate on date and time data types, including GETDATE, CURRENT_TIMESTAMP, GETUTCDATE, SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, CAST, CONVERT, SWITCHOFFSET, TODATETIMEOFFSET, DATEADD, DATEDIFF, DATEPART, YEAR, MONTH, DAY, DATENAME, various FROMPARTS functions, and EOMONTH.
The functions SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET were introduced in SQL Server 2008. Existing functions were enhanced to support the newer types and parts. The various FROMPARTS functions and the EOMONTH function were introduced in SQL Server 2012.
Current Date and Time
The following niladic (parameterless) functions return the current date and time values in the system where the SQL Server instance resides: GETDATE, CURRENT_TIMESTAMP, GETUTCDATE, SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET. Table 2-3 provides the description of these functions.
Table 2-3. Functions Returning Current Date and Time
Function |
Return Type |
Description |
GETDATE |
DATETIME |
Current date and time |
CURRENT_TIMESTAMP |
DATETIME |
Same as GETDATE but ANSI SQL–compliant |
GETUTCDATE |
DATETIME |
Current date and time in UTC |
SYSDATETIME |
DATETIME2 |
Current date and time |
SYSUTCDATETIME |
DATETIME2 |
Current date and time in UTC |
SYSDATETIMEOFFSET |
DATETIMEOFFSET |
Current date time including time zone |
Note that you need to specify empty parentheses with all functions that should be specified without parameters, except the ANSI function CURRENT_TIMESTAMP. Also, because CURRENT_TIMESTAMP and GETDATE return the same thing but only the former is standard, it is recommended that you use the former. This is a practice that I try to follow in general—when I have several options that do the same thing with no functional or performance difference, and one is standard but others aren’t, my preference is to use the standard option.
The following code demonstrates using the current date and time functions.
SELECT GETDATE() AS [GETDATE], CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP], GETUTCDATE() AS [GETUTCDATE], SYSDATETIME() AS [SYSDATETIME], SYSUTCDATETIME() AS [SYSUTCDATETIME], SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];
As you probably noticed, none of the functions return only the current system date or only the current system time. However, you can get those easily by converting CURRENT_TIMESTAMP or SYSDATETIME to DATE or TIME like this.
SELECT CAST(SYSDATETIME() AS DATE) AS [current_date], CAST(SYSDATETIME() AS TIME) AS [current_time];
The CAST, CONVERT, and PARSE Functions and Their TRY_ Counterparts
The CAST, CONVERT and PARSE functions are used to convert an input value to some target type. If the conversion succeeds, the functions return the converted value; otherwise, they cause the query to fail. The three functions have counterparts called TRY_CAST, TRY_CONVERT, and TRY_PARSE, respectively. Each version with the prefix TRY_ accepts the same input as its counterpart, and does the same thing; the difference is that if the input isn’t convertible to the target type, the function returns a NULL instead of failing the query.
The functions TRY_CAST, TRY_CONVERT, PARSE, and TRY_PARSE were added in SQL Server 2012.
Syntax
CAST(value AS datatype)
TRY_CAST(value AS datatype)
CONVERT (datatype, value [, style_number])
TRY_CONVERT (datatype, value [, style_number])
PARSE (value AS datatype [USING culture])
TRY_PARSE (value AS datatype [USING culture])
All three base functions convert the input value to the specified target datatype. In some cases, CONVERT has a third argument with which you can specify the style of the conversion. For example, when you are converting from a character string to one of the date and time data types (or the other way around), the style number indicates the format of the string. For example, style 101 indicates ‘MM/DD/YYYY’, and style 103 indicates ‘DD/MM/YYYY’. You can find the full list of style numbers and their meanings in SQL Server Books Online under “CAST and CONVERT.” Similarly, where applicable, the PARSE function supports indication of a culture—for example, ‘en-US’ for U.S. English and ‘en-GB’ for British English.
As mentioned earlier, when you are converting from a character string to one of the date and time data types, some of the string formats are language dependent. I recommend either using one of the language-neutral formats or using the CONVERT/PARSE functions and explicitly specifying the style number or culture. This way, your code is interpreted the same way regardless of the language of the logon running it.
Note that CAST is ANSI and CONVERT and PARSE aren’t, so unless you need to use the style number or culture, it is recommended that you use the CAST function; this way, your code is as standard as possible.
Following are a few examples of using the CAST, CONVERT, and PARSE functions with date and time data types. The following code converts the character string literal ‘20090212’ to a DATE data type.
SELECT CAST('20090212' AS DATE);
The following code converts the current system date and time value to a DATE data type, practically extracting only the current system date.
SELECT CAST(SYSDATETIME() AS DATE);
The following code converts the current system date and time value to a TIME data type, practically extracting only the current system time.
SELECT CAST(SYSDATETIME() AS TIME);
As suggested earlier, if you need to work with the DATETIME or SMALLEDATETIME types (for example, to be compatible with systems using versions earlier than SQL Server 2008) and want to represent only a date or only a time, you can “zero” the irrelevant part. In other words, to work only with dates, you set the time to midnight. To work only with time, you set the date to the base date January 1, 1900.
The following code converts the current date and time value to CHAR(8) by using style 112 (‘YYYYMMDD’).
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);
For example, if the current date is February 12, 2009, this code returns ‘20090212’. Remember that this style is language neutral, so when the code is converted back to DATETIME, you get the current date at midnight.
SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);
Similarly, to zero the date portion to the base date, you can first convert the current date and time value to CHAR(12) by using style 114 (‘hh:mm:ss.nnn’).
SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);
When the code is converted back to DATETIME, you get the current time on the base date.
SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);
As for using the PARSE function, here are a couple of examples that I also demonstrated previously in this chapter.
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US');
SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');
The first parses the input string by using a U.S. English culture, and the second by using a British English culture.
The SWITCHOFFSET Function
The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone.
Syntax
SWITCHOFFSET(datetimeoffset_value, time_zone)
For example, the following code adjusts the current system datetimeoffset value to time zone -05:00.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
So if the current system datetimeoffset value is February 12, 2009 10:00:00.0000000 -08:00, this code returns the value February 12, 2009 13:00:00.0000000 -05:00.
The following code adjusts the current datetimeoffset value to UTC.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00');
Assuming the aforementioned current datetimeoffset value, this code returns the value February 12, 2009 18:00:00.0000000 +00:00.
The TODATETIMEOFFSET Function
The TODATETIMEOFFSET function sets the time zone offset of an input date and time value.
Syntax
TODATETIMEOFFSET(date_and_time_value, time_zone)
This function is different from SWITCHOFFSET in that its first input will usually be a date and time type that is not offset aware. This function simply merges the input date and time value with the specified time zone offset to create a new datetimeoffset value.
You will typically use this function when migrating non-offset-aware data to offset-aware data. Imagine that you have a table holding local date and time values in an attribute called dt of a DATETIME data type and the offset in an attribute called theoffset. You then decide to merge the two to one offset-aware attribute called dto. You alter the table and add the new attribute. Then you update it to the result of the expression TODATETIMEOFFSET(dt, theoffset). Then you can drop the two existing attributes dt and theoffset.
The DATEADD Function
The DATEADD function allows you to add a specified number of units of a specified date part to an input date and time value.
Syntax
DATEADD(part, n, dt_val)
Valid values for the part input include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, and nanosecond. You can also specify the part in abbreviated form, such as yy instead of year. Refer to SQL Server Books Online for details.
The return type for a date and time input is the same type as the input’s type. If this function is given a string literal as input, the output is DATETIME.
For example, the following code adds one year to February 12, 2009.
SELECT DATEADD(year, 1, '20090212');
This code returns the following output.
----------------------- 2010-02-12 00:00:00.000
The DATEDIFF Function
The DATEDIFF function returns the difference between two date and time values in terms of a specified date part.
Syntax
DATEDIFF(part, dt_val1, dt_val2)
For example, the following code returns the difference in terms of days between two values.
SELECT DATEDIFF(day, '20080212', '20090212');
This code returns the output 366.
Ready for a bit more sophisticated use of the DATEADD and DATEDIFF functions? You can use the following code in versions prior to SQL Server 2008 to set the time component of the current system date and time value to midnight.
SELECT
DATEADD(
day,
DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101');
This is achieved by first using the DATEDIFF function to calculate the difference in terms of whole days between an anchor date at midnight (‘20010101’ in this case) and the current date and time (call that difference diff). Then, the DATEADD function is used to add diff days to the anchor. You get the current system date at midnight.
Interestingly, if you use this expression with a month part instead of a day, and make sure to use an anchor that is the first day of a month (as in this example), you get the first day of the current month.
SELECT
DATEADD(
month,
DATEDIFF(month, '20010101', CURRENT_TIMESTAMP), '20010101');
Similarly, by using a year part and an anchor that is the first day of a year, you get back the first day of the current year.
If you want the last day of the month or year, simply use an anchor that is the last day of a month or year. For example, the following expression returns the last day of the current month.
SELECT
DATEADD(
month,
DATEDIFF(month, '19991231', CURRENT_TIMESTAMP), '19991231');
Note that in SQL Server 2012 there’s a simpler way to get the last day of the month: by using a new function called EOMONTH. I’ll describe it shortly.
The DATEPART Function
The DATEPART function returns an integer representing a requested part of a date and time value.
Syntax
DATEPART(part, dt_val)
Valid values for the part argument include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, TZoffset, and ISO_WEEK. The last four parts are available in SQL Server 2008 and SQL Server 2012. As I mentioned earlier, you can use abbreviations for the date and time parts, such as yy instead of year, mm instead of month, dd instead of day, and so on.
For example, the following code returns the month part of the input value.
SELECT DATEPART(month, '20090212');
This code returns the integer 2.
The YEAR, MONTH, and DAY Functions
The YEAR, MONTH, and DAY functions are abbreviations for the DATEPART function returning the integer representation of the year, month, and day parts of an input date and time value.
Syntax
YEAR(dt_val)
MONTH(dt_val)
DAY(dt_val)
For example, the following code extracts the day, month, and year parts of an input value.
SELECT
DAY('20090212') AS theday,
MONTH('20090212') AS themonth,
YEAR('20090212') AS theyear;
This code returns the following output.
theday themonth theyear ----------- ----------- ----------- 12 2 2009
The DATENAME Function
The DATENAME function returns a character string representing a part of a date and time value.
Syntax
DATENAME(dt_val, part)
This function is similar to DATEPART and in fact has the same options for the part input. However, when relevant, it returns the name of the requested part rather than the number. For example, the following code returns the month name of the given input value.
SELECT DATENAME(month, '20090212');
Recall that DATEPART returned the integer 2 for this input. DATENAME returns the name of the month, which is language dependent. If your session’s language is one of the English languages (such as U.S. English or British English), you get back the value ‘February’. If your session’s language is Italian, you get back the value ‘febbraio’. If a part is requested that has no name, but only a numeric value (such as year), the DATENAME function returns its numeric value as a character string. For example, the following code returns ‘2009’.
SELECT DATENAME(year, '20090212');
The ISDATE Function
The ISDATE function accepts a character string as input and returns 1 if it is convertible to a date and time data type and 0 if it isn’t.
Syntax
ISDATE(string)
For example, the following code returns 1.
SELECT ISDATE('20090212');
And the following code returns 0.
SELECT ISDATE('20090230');
The FROMPARTS Functions
The FROMPARTS functions were introduced in SQL Server 2012. They accept integer inputs representing parts of a date and time value and construct a value of the requested type from those parts.
Syntax
DATEFROMPARTS (year, month, day)
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
These functions make it easier for applications to construct date and time values from the different components, and they also simply migrate from other environments that already support similar functions. The following code demonstrates the use of these functions.
SELECT DATEFROMPARTS(2012, 02, 12), DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7), DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997), DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7), SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30), TIMEFROMPARTS(13, 30, 5, 1, 7);
The EOMONTH Function
The EOMONTH function was introduced in SQL Server 2012. It accepts an input date and time value and returns the respective end-of-month date, at midnight, as a DATE data type. The function also supports an optional second argument indicating how many months to add.
Syntax
EOMONTH(input [, months_to_add])
For example, the following code returns the end of the current month.
SELECT EOMONTH(SYSDATETIME());
The following query returns orders placed on the last day of the month.
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate = EOMONTH(orderdate);