In many scenarios, date and time information is loaded and stored in the text format. Converting these values to the date/time type is a standard requirement in most business applications for analysis needs or performance improvement if we query the data by date values.
In SQL Server, we can do this in two general ways – using implicit or explicit conversion.
Implicit conversion is not visible to the end-user, data is converted during load or data retrieval, and without using any dedicated function or procedure.
Explicit conversions use integrated or user-defined functions or procedures, mostly by implementing CAST or CONVERT built-in functions or their extensions.
This article will demonstrate implicit and explicit conversion methods. In case of explicit conversion, except CAST and CONVERT, we’ll show newer additions to SQL Server – TRY_CAST, TRY_CONVERT(), and TRY_PARSE() functions.
Implicit conversions are not visible to end-users, which is demonstrated by the example below:
USE AdventureWorks2019 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM information_schema.columns WHERE '1' = 1
In SQL Server, when implicitly converting dates, we depend on date format and server language settings. The only exception is storing dates in ISO formats ( “yyyyMMdd” or “yyyy-MM-ddTHH:mm:ss(.mmm)” ). In this case, they are always converted, no matter what regional and language settings are defined on the server, as seen in the example below:
— This example will work since the date is in the ISO format
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM information_schema.columns WHERE GETDATE() > '20000101'
— This example will throw an exception since the date is in the DDMMYYYY format
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM information_schema.columns WHERE GETDATE() > '01012020'
A very detailed overview of implicit rules for date conversion is available in this blog post.
If you don’t want to depend on the input format and server settings, you can use explicit conversion functions, including CONVERT(), CAST(), and PARSE() with some extensions.
The CAST Function
When people encounter conversion problems for the first time, they first try the CAST() function – it is the most basic and easiest one to use, although lacking more advanced functionality.
CAST() takes an input value and converts it to the specified data type with an optional data type length. The example of usage, in the context of date conversions, is below:
SELECT CAST('06/08/2021' as date) as StringToDate , CAST(GETDATE() as VARCHAR(50)) as DateToString
The rule of language settings applies as well to implicit conversions, so CAST will work correctly only on ISO formats or formats supported by the current regional/server language settings.
The CONVERT Function
If we need more functionality, especially if we want to specify the conversion format, we need to use the CONVERT function. It takes three input parameters – the destination data type, the input value, and the conversion format (optional parameter). If there is no format defined, it will act like the CAST function, but if there is any input, it will use that conversion style.
The example of using CONVERT with the custom format is below:
DECLARE @VarDate VARCHAR(10) SET @VarDate = '06-08-2021' SELECT CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @VarDate, 103), 102))
The PARSE Function
CAST and CONVERT are native SQL Server functions. With newer versions of SQL Server, we can use the PARSE function, which is the CLR (.NET) function.
The basic syntax of the PARSE function is:
PARSE(<value> AS <data type> [USING <culture>]).
If the input parameter “<culture>” is not defined, PARSE will behave the same as the CAST function. But if we put the proper value, PARSE will try to convert using the string.
— Parsing string with Arabic culture setting
SELECT PARSE('06/08/2021' AS DATE USING 'AR-LB')
TRY_CAST, TRY_CONVERT and TRY_PARSE Functions
If we send invalid date strings to CAST, CONVERT, or PARSE functions, we will get an exception. Sometimes this is acceptable, but sometimes we want to process without errors. To support this scenario, SQL Server comes with three functions that check in advance if the value can be parsed. If so, they will return the converted value, if not – they will return NULL. The example is below:
— The first value will be converted, the second will be NULL, no exception thrown
SELECT TRY_CAST('06/08/2021' as date), TRY_CAST('01/01/0000' as date)
The List of Available Conversion Formats
You can get the list of all available conversion formats in the official online documentation. But sometimes it is more convenient to get it programmatically since the formats change over SQL Server versions. The function below returns the list of all valid formats:
DECLARE @X INT = 0 DECLARE @DATE_TEST DATETIME = '2021-08-06 00:00:01.001' CREATE TABLE #AvaliableFormats (FormatOption INT, FormatOutput NVARCHAR(255)) WHILE (@X <= 300 ) BEGIN BEGIN TRY INSERT INTO #AvaliableFormats SELECT CONVERT(NVARCHAR, @X), CONVERT(NVARCHAR,@DATE_TEST, @X) SET @X = @X + 1 END TRY BEGIN CATCH; SET @X = @X + 1 IF @X >= 300 BEGIN BREAK END END CATCH END SELECT * FROM #AvaliableFormats DROP TABLE #AvaliableFormats
When to Use CAST, CONVERT, or PARSE Functions
The table below shows an overview of the differences between these three functions:
|Description||Changes one data type to another||Changes one data type to another||Retrieves expression result, in SQL data type that was sent as an input parameter|
|Input values||Any list of characters||Any list of characters||String|
|Output values||Converted value to requested data type||Converted value to requested data type||Converted value to requested data type|
|Possible transformations||Any two valid data types||Any two valid data types||Input has to be string value and output can be only the number or date/time type|
|Requires .NET installed||No||No||Yes|
CAST has been available in SQL Server for a long time, and it is present in many other DBMS implementations. If you need portability and are satisfied with the limitations of this function, use CAST in your code.
CONVERT is very useful if you need to specify custom data formats since CAST does not support “style” arguments and you cannot use PARSE to convert from date to the Varchar (string) value.
Although there are minuses to using .NET functions, primarily related to performance, the necessity of installation on a server, and data type conversion limitations, you’ll need PARSE if you have string inputs that cannot fit in the provided custom formats.
One example is having days or months in custom string formats instead of numeric values. When you need to specify custom format logic, you cannot do it with CAST and CONVERT, but PARSE will suit.
DATEPART and DATENAME Functions
There is almost no business application that does not retrieve or save date/time data, at least for logging purposes. In most cases, we need to display day or month names in a string format, or we need to display some kind of calculation involving date and time, for example, week number.
SQL Server offers two functions that can help with similar activities – DATEPART and DATENAME. Both require two input parameters – the time part to be extracted and the input date value.
DATEPART returns a number, while DATANAME returns a string that can be only the day in a week and a month name. The example of all possible use scenarios of these functions is below:
DECLARE @InputValue DATETIME2(7) SET @InputValue = '2021-08-08 13:05:00.0000112' SELECT DATEPART(ISO_WEEK,@InputValue) AS ISO_WEEK , DATEPART(NANOSECOND,@InputValue) AS NANOSECOND , DATEPART(MICROSECOND,@InputValue) AS MICROSECOND , DATEPART(MS,@InputValue) AS MILISECOND , DATEPART(SS,@InputValue) AS SECOND , DATEPART(MINUTE,@InputValue) AS MINUTE , DATEPART(HH,@InputValue) AS HOUR , DATEPART(DW,@InputValue) AS DAYINWEEK , DATEPART(WEEK,@InputValue) AS WEEK , DATEPART(DAY,@InputValue) AS DAY , DATEPART(DAYOFYEAR,@InputValue) AS DAYOFYEAR , DATEPART(MM,@InputValue) AS MONTH , DATEPART(QUARTER,@InputValue) AS QUARTER , DATEPART(YYYY,@InputValue) AS YEAR , DATENAME(ISO_WEEK,@InputValue) AS ISOWEEK , DATENAME(TZoffset,@InputValue) AS TZoffset , DATENAME(nanosecond,@InputValue) AS NANOSECOND , DATENAME(microsecond,@InputValue) AS MICROSECOND , DATENAME(millisecond,@InputValue) AS MICROSECOND , DATENAME(ss,@InputValue) AS SECOND , DATENAME(minute,@InputValue) AS MINUTE , DATENAME(HOUR,@InputValue) AS HOUR , DATENAME(weekday,@InputValue) AS DAYINWEEK , DATENAME(wk,@InputValue) AS WEEK , DATENAME(d,@InputValue) AS DAY , DATENAME(dayofyear,@InputValue) AS DAYOFYEAR , DATENAME(m,@InputValue) AS MONTH , DATENAME(quarter,@InputValue) AS QUARTER , DATENAME(YYYY,@InputValue) AS YEAR
Building a Calendar Table
The standard case in data warehousing is the initial creation of a calendar table that would be later used to create time dimensions for reporting or data processing.
The code below is an example of how you can use these two functions to create such a table:
DECLARE @StartDate DATE = '01/01/2018', @EndDate DATE = '12/31/2021' DECLARE @CalendarData TABLE ( DateValue DATE PRIMARY KEY, MonthNO INT, DateNO INT, DateOfYear INT, WeekNO INT, DayOfWeekNO INT, NameOfMonth NVARCHAR(50), NameOfDay NVARCHAR(50) ) WHILE DATEDIFF(DAY,@StartDate,@EndDate) >= 0 BEGIN INSERT INTO @CalendarData (DateValue, MonthNO, DateNO, DateOfYear, WeekNO, DayOfWeekNO , NameOfMonth, NameOfDay) SELECT @StartDate , DATEPART(MONTH,@StartDate) , DATEPART(DAY,@StartDate) , DATEPART(DAYOFYEAR,@StartDate) , DATEPART(WEEK,@StartDate) , DATEPART(DW,@StartDate) , DATENAME(MONTH,@StartDate) , DATENAME(DW,@StartDate) SELECT @StartDate = DATEADD(DAY,1,@StartDate) END SELECT * FROM @CalendarData
Data type conversion, especially date-time operations, needs to be done carefully and thoroughly tested. If the data format changes during application operations or data exchanges, it might cause crashes and exceptions.
It is crucial to be aware of possible changes and differences between development and production environments, especially in regional settings in different instances and installations. If available, it is recommended to use TRY functions and handle NULL logic programmatically, logging but not stopping application execution, except if processed dates are not key business information.
You can use all integrated functions, like DATEPART and DATENAME, rather than writing your own, since they are thoroughly tested and offer the best performance.Last modified: March 18, 2022