SQL Server data conversions from date/time values to character types

T-SQL supports two built-in methods for converting date/time data to character data and vice versa. SQL Server expert Robert Sheldon takes you through a step-by-step process for using each method -- implicit and explicit conversions. The steps include using the handy CAST and CONVERT functions for converting date/time values.

In this article, I explain how to convert DATETIME and SMALLDATETIME data types to character data and how character data can be converted to date/time data. Specifically, the article describes how Transact-SQL supports two built-in functions for converting data in SQL Server -- implicit conversion and explicit conversion. In a previous tip, Basics of DATETIME and SMALLDATETIME in SQL Server 2005, I described how SQL Server uses the...

DATETIME and SMALLDATETIME data types to store date/time data. In this article, I'll explain how it can be converted to character data and how character data can be converted to datetime data and how Transact-SQL supports two methods for performing these data conversions -- implicit conversion and explicit conversion.

This article assumes that you have a working knowledge of T-SQL and SQL Server and it is concerned only with converting date/time data to character data and vice versa. However, you can convert other types of values, such as INT to DATETIME. In most cases, though, you'll work primarily with character-date/time conversions.

Implicitly converting data

When you insert data into a DATETIME or SMALLDATETIME column, SQL Server automatically attempts to convert the data if it is of a different type. For example, if you insert a CHAR value into a DATETIME column, SQL Server will convert the data – if the value is in an  acceptable format. If you insert a DATETIME value into a CHAR column, SQL Server will automatically convert that as well.

Let's look at a couple of implicit conversions to better understand how this works. To demonstrate these conversions, I used the following code to create the LogInfo table in the AdventureWorks sample database:

USE AdventureWorks
GO
CREATE TABLE dbo.LogInfo
(
LogID INT PRIMARY KEY,
LogEvent NVARCHAR(30) NOT NULL,
Post_DateTime DATETIME NOT NULL,
Post_SmallDateTime SMALLDATETIME NOT NULL,
Post_NVarChar NVARCHAR(25) NOT NULL
)

The table includes three columns to hold date/time information: Post_DateTime, Post_SmallDateTime, and Post_NVarChar. The column names reflect the names of the data types used to define the columns. Now let's insert data into those columns:

INSERT INTO LogInfo
SELECT DatabaseLogID, [Event],
PostTime, PostTime, PostTime
FROM dbo.DatabaseLog

The statement retrieves data from the DatabaseLog table (in the AdventureWorks database) and inserts it into the LogInfo table. The PostTime column in the source table is defined with the DATETIME data type. Notice that this column is used to insert data into each of the date/time columns in the LogInfo table.

After you populate the table, you can use the following SELECT statement to retrieve the first row from the LogInfo table:

SELECT * from dbo.LogInfo
WHERE LogID = 1

The SELECT statement returns values for all columns for the row with the LogID value of 1. The following results show how the data is stored in the table:

LogID LogEvent Post_DateTime Post_SmallDateTime Post_NVarChar
1 CREATE_TABLE 2005-10-14 01:58:27.567 2005-10-14 01:58:00 Oct 14 2005 1:58AM

As you can see, each date/time value is a little different. The Post_DateTime column stores the full date and time values. However, as expected, the Post_SmallDateTime column stores a shortened version of the time (with 00 used to mark the seconds). Finally, the Post_NVarChar stores a string value in a format quite different from the other two. 

By default, when SQL Server converts a DATETIME or SMALLDATETIME value to a character value, it uses the format shown above (Oct 14 2005 1:58AM). Later in the article, you'll see it's possible to change this format to a number of other available formats. For now, however, the important point is how SQL Server implicitly converts date/time values. Now let's take a look at explicit data conversions in SQL Server.

Explicitly converting data

To explicitly convert date/time values, you must use either the CAST or CONVERT Transact-SQL function. Because the CAST function is the simpler of the two, let's start with that. The following SELECT statement uses the CAST function to convert character data in the Post_NVarChar column to a DATETIME value:

SELECT LogID, LogEvent,
CAST(Post_NVarChar AS DATETIME) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

When you use the CAST function, you must specify the name of the source column (or some other expression), the AS keyword and the data type that the value will be converted to -- in this case it's DATETIME. When you run this statement, the value is converted, as shown in the following results:

LogID LogEvent Post_Converted
1 CREATE_TABLE 2005-10-14 01:58:00.000

(1 row(s) affected)

Notice, the Post_Converted column (the alias assigned to the column in the SELECT clause) is in the expected DATETIME format, with the full date and time values, down to the milliseconds. However, the seconds are represented as 00.000. This is because, when SQL Server converted the original value, it dropped the seconds and stored only the hours and minutes. When you convert the value back to DATETIME, SQL Server interprets the seconds as 00.000.

However, if the date/time value is stored as a string in the format used by the DATETIME data type, SQL Server preserves the seconds. For example, the following SELECT statement uses the CAST function to convert a string value to DATETIME:

SELECT CAST('2005-10-14 01:58:27.567' AS DATETIME) AS [Date/Time]

The following results show that the seconds and milliseconds are now preserved:

Date/Time
2005-10-14 01:58:27.567

(1 row(s) affected)

In addition to explicitly converting character data to a DATETIME (or SMALLDATETIME) value, you can use the CAST function to convert DATETIME data to character data. The following SELECT statement uses the CAST function to retrieve data from the Post_DateTime column:

SELECT LogID, LogEvent,
CAST(Post_DateTime AS VARCHAR(20)) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

As you can see, the statement converts the value to VARCHAR, as shown in the following results:

LogID LogEvent Post_Converted
1 CREATE_TABLE Oct 14 2005 1:58AM

(1 row(s) affected)

Notice that the converted value is now in the format you saw earlier, when SQL Server implicitly converted the DATETIME value to NVARCHAR.

Now that you know how to use the CAST function, let's move on to the CONVERT function. At its most basic, the CONVERT function returns the same results as the CAST function. For example, the following statement converts the Post_DateTime value to VARCHAR, as in the previous example:

SELECT LogID, LogEvent,
CONVERT(VARCHAR(20), Post_DateTime) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

However, notice that the arguments in the CONVERT function appear in a different order than the order in the CAST function. When using CONVERT, you first specify the target data type (VARCHAR) followed by the name of the source column (Post_DateTime) with the two arguments separated by a comma, rather than the AS keyword. When you run the statement, you receive the following results:

LogID LogEvent Post_Converted
1 CREATE_TABLE Oct 14 2005 1:58AM

(1 row(s) affected)

The results are identical to the preceding example. But suppose you want to display your date/time value in a format other than what we've seen so far (Oct 14 2005 1:58AM). In that case, you add a third argument to the CONVERT function to specify the new format, as shown in the following example:

SELECT LogID, LogEvent,
CONVERT(VARCHAR(20), Post_DateTime, 101) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

Notice that 101 has been added as the third argument. When specifying a format, you must use one of the predefined codes supported by T-SQL to represent the format you want to use. In this case, the 101 returns the format shown in the following results:

LogID LogEvent Post_Converted
1 CREATE_TABLE 10/14/2005

(1 row(s) affected)

The Post_Converted value is now in the form of 10/14/2005, which is the format represented by the code 101. If you want your results to be displayed in a format similar to how a DATETIME value is displayed, specify the code 121, as in the following example:

SELECT LogID, LogEvent,
CONVERT(VARCHAR(25), Post_DateTime, 121) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

Now the results are returned with the full date and time values, down to the millisecond:

LogID LogEvent Post_Converted
1 CREATE_TABLE 2005-10-14 01:58:27.567

(1 row(s) affected)

T-SQL supports a number of predefined formats. For a full listing of the formats and codes you use to call each format, see the CAST and CONVERT (Transact-SQL) topic in Microsoft SQL Server Books Online.

Now let's look at a different example. In the following SELECT statement, we use the CONVERT function to convert the Post_SmallDateTime column to a VARCHAR column:

SELECT LogID, LogEvent,
CONVERT(VARCHAR(25), Post_SmallDateTime, 121) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

As in the previous example, the date/time value is displayed in the 121 format, like this:

LogID LogEvent Post_Converted
1 CREATE_TABLE 2005-10-14 01:58:00.000

(1 row(s) affected)

Notice, however, that because the date/time value was retrieved from a SMALLDATETIME column, the seconds in the time value are 00.000, as you would expect with SMALLDATETIME. Here's how to truncate the seconds by specifying a shorter length for the VARCHAR data type:

SELECT LogID, LogEvent,
CONVERT(VARCHAR(16), Post_SmallDateTime, 121) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

Now the data type argument for the CONVERT function shows VARCHAR(16), rather than VARCHAR(25), as in the previous example. The following results show how that value has been truncated so the seconds are no longer displayed:

LogID LogEvent Post_Converted
1 CREATE_TABLE 2005-10-14 01:58

(1 row(s) affected)

That's all there is to explicitly convert date/time values. Both the CAST and CONVERT functions can be handy tools when retrieving these values. (Note that the functions can be used to convert other types of values as well.) In upcoming articles, I'll describe how to retrieve specific information from date/time columns and how to calculate date/time values. In the meantime, you should now have a solid foundation in how to convert those values and display them in the format that's most useful to you.


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

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.

This was first published in August 2008

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close