Tip

Using SQL Server datetime functions GETDATE, DATENAME and DATEPART

Transact-SQL includes a set of functions that let you retrieve the current date and time or retrieve the individual parts of a DATETIME or SMALLDATETIME value. For example, you can extract the day, month or year from a datetime value, as well as the quarter, week, hour or even the millisecond. In this article, I describe each of these functions and provide examples that demonstrate how to use these functions to retrieve datetime data in SQL Server. Note that this article assumes that you have a working knowledge of T-SQL and the DATETIME and SMALLDATETIME data types. For more information about these types, see part one in this series,

    Requires Free Membership to View

Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005.

Retrieving the current date and time

One of the handiest datetime functions in T-SQL is GETDATE, which retrieves the current date and time based on the clock settings on the local system. To use GETDATE, simply call the function in your T-SQL statement without specifying any arguments, as in the following example:

SELECT GETDATE() AS [Current Date/Time]

In this case, I use GETDATE in the SELECT list to retrieve the date/time value. (Note that you must include the ending set of parentheses even if you don't pass in any arguments.) The statement returns results similar to the following:

Current Date/Time
2008-07-29 10:45:13.327

By default, the GETDATE function returns the datetime value in the format shown here. However, you can change the format of the results by using the CONVERT function. For information about using CONVERT, refer to part two in this tip series Data conversions from date/time values to character types.

Another Transact-SQL function that is just as easy to use is GETUTCDATE, which retrieves the current Coordinated Universal Time (UTC) -- also referred to as Greenwich Mean Time. The retrieved value is based on the clock and time zone settings on the local system. As you saw with GETDATE, you call GETUTCDATE within your Transact-SQL statement without including any arguments, as shown in the following example:

 SELECT GETUTCDATE() AS [UTC Date/Time]

When you run this statement, you receive results similar to the following:

UTC Date/Time
2008-07-29 17:45:13.327

Notice that the time returned here is seven hours later than the time shown in the previous example. I ran both of these statements at the same time on a system configured for the Pacific time zone (during daylight savings time).

As you've seen in the last two examples, the functions are included within the SELECT list. However, the functions can be especially beneficial when using them to define a default value in your table definition. For example, the following three statements create the Orders table -- including a DATETIME column (OrderDate) -- insert data into the table and retrieve that data:

CREATE TABLE Orders
(
OrderID INT PRIMARY KEY IDENTITY,
Product VARCHAR(30) NOT NULL,
OrderAmt INT NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO
INSERT INTO Orders (Product, OrderAmt)
VALUES('Test Product', 12)
GO
SELECT * FROM Orders

The OrderDate column definition includes a DEFAULT clause that specifies GETDATE as the default value. As a result, when you insert a row into the table, the current date and time are automatically inserted into the column, as shown in the results returned by the SELECT statement:

 

OrderID Product OrderAmt OrderDate
1 Test Product 12 2008-07-29 10:46:47.420

You can use the information as a timestamp in order to track when records are added and to assist in auditing the data, if necessary. This is also handy for other operations that use the timestamp when retrieving data. For example, an extract, transform and load (ETL) process might reference the timestamp when determining whether to extract or update data.

Retrieving the year, month or day

In some cases, you might want to retrieve the year, month or day from a DATETIME or SMALLDATETIME value. One approach is to use the YEAR, MONTH or DAY function to retrieve the necessary data (as an integer). The following SELECT statement is an example of how this works:

SELECT YEAR(PostTime) AS [Year],
MONTH(PostTime) AS [Month],
DAY(PostTime) AS [Day]
FROM DatabaseLog
WHERE DatabaseLogID = 1

The SELECT clause includes three column expressions. The first one uses the YEAR function to retrieve the year from the PostTime column in the DatabaseLog table (in the AdventureWorks sample database). When you call the YEAR function, you specify the column name (or other expression) as an argument to the function. The MONTH and DAY functions work the same way. The second column expression in the SELECT clause uses the MONTH function to retrieve the month from the PostTime column, and the third expression uses DAY to retrieve the day. The following results show you the type of information that the statement returns:

Year Month Day
2005 10 14

Each value is extracted from the PostTime column and returned as an integer. (The value stored in the table is 2005-10-14 01:58:27.567.)

These functions are an easy way to retrieve the year, month or day, but, in some cases, you might want more control over the type of values returned as well as the format of those values. In addition, you might want to extract the time from the date/time value. Fortunately, Transact-SQL supports functions that provide this type of functionality.

Retrieving parts of a date/time value

Like the YEAR, MONTH and DAY functions, the DATEPART function returns an integer representing a specific part of the date/time value. For example, the following SELECT statement returns the same results as the preceding example:

SELECT DATEPART(yy, PostTime) AS [Year],
DATEPART(mm, PostTime) AS [Month],
DATEPART(dd, PostTime) AS [Day]
FROM DatabaseLog
WHERE DatabaseLogID = 1

The first thing to note is that, when you call DATEPART, you specify two arguments. The first argument determines the date/time component to retrieve, and the second argument is the source column. For the first argument, you must use one of the supported abbreviations to specify the datetime part. The following table lists the date/time parts you can retrieve and the abbreviations you must use to retrieve those parts:

Date/time part Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
day of year dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

For some datetime parts, more than one abbreviation is supported. For example, you can use "yy" or "yyyy" as your first DATEPART argument to retrieve the year from the date/time value. Notice that the table includes abbreviations for date/time parts other than year, month or day. In other words, you can retrieve the quarter, the day of the year, the week of the year, and the weekday as shown in the following SELECT statement:

SELECT DATEPART(qq, PostTime) AS [Quarter],
DATEPART(dy, PostTime) AS [DayOfYear],
DATEPART(wk, PostTime) AS [Week],
DATEPART(dw, PostTime) AS [Weekday]
FROM DatabaseLog
WHERE DatabaseLogID = 1

As in the preceding example, each instance of DATEPART includes two arguments: the date/time part abbreviation and the source column. The statement returns the following results:

Quarter DayOfYear Week Weekday
4 287 42 6

Notice that the weekday is shown as 6. By default, SQL Server begins the week with Sunday, so weekday 6 is equivalent to Friday.

The preceding two examples retrieved only values related to dates. However, as the table below shows, you can also retrieve data related to time:

SELECT DATEPART(hh, PostTime) AS [Hour],
DATEPART(mi, PostTime) AS [Minute],
DATEPART(ss, PostTime) AS [Second],
DATEPART(ms, PostTime) AS [Millisecond]
FROM DatabaseLog
WHERE DatabaseLogID = 1

In this case, the statement is retrieving the hour, minute, second and millisecond, as shown in the following results:

Hour Minute Second Millisecond
1 58 27 567

The primary limitation of the DATEPART function is that it returns only integers, which is why Friday is shown as 6. However, if you want to display actual names of days and months, you can use the DATENAME function. The DATENAME function works exactly like the DATEPART function. DATENAME takes the same number of arguments and supports the same abbreviations. For example, if you want to retrieve the year, month and day, as you saw in an earlier example, you simply replace DATEPART with DATENAME:

SELECT DATENAME(yy, PostTime) AS [Year],
DATENAME(mm, PostTime) AS [Month],
DATENAME(dd, PostTime) AS [Day]
FROM DatabaseLog
WHERE DatabaseLogID = 1

Now your results will look like the following:

Year Month Day
2005 October 14

The month value is now October, rather than 10. The year and day, however, remain integers because that's the only way to represent them. You can also use the DATENAME function for other date/time components, as in the following example:

SELECT DATENAME(qq, PostTime) AS [Quarter],
DATENAME(dy, PostTime) AS [DayOfYear],
DATENAME(wk, PostTime) AS [Week],
DATENAME(dw, PostTime) AS [Weekday]
FROM DatabaseLog
WHERE DatabaseLogID = 1

Once again, I've replaced DATEPART with DATENAME, but changed nothing else. The statement returns the following results.

Quarter DayOfYear Week Weekday
4 287 42 Friday

Notice that the quarter, day of the year and week are still integers, but the weekday now says Friday, rather than 6. You can also use DATENAME to retrieve the time components of a date/time value, but the results will always be integers, as you would expect.

That's all there is to using the DATEPART and DATENAME functions, along with the other functions that let you retrieve date/time values. You can use any of these functions individually or in conjunction with each other to concatenate values. I encourage you to experiment with these functions to gain a clear sense of how each one works. In my next tip (part four) I'll show you how to perform calculations on these values in order to add dates and determine date ranges. In the meantime, you can find additional information and examples for each of the functions I've described here at Microsoft SQL Server Books Online.


Tip Series: Working with date/time data types in SQL Server

 Part 1: Basics for working with DATETIME and SMALLDATETIME
 Part 2: Data conversions from date/time values to character types
 Part 3: Using datetime functions GETDATE, DATENAME and DATEPART
 Part 4: Using DATEADD and DATEDIFF to calculate datetime values
 Part 5: New datetime data types in SQL Server 2008

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.


This was first published in September 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.