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.
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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
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
Check back for part 5 in this series: COMING SOON!
| 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. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at www.rhsheldon.com. |
|
