Varchar To Date Conversions In SQL Server

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 Conversion

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'

Explicit Conversion

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.

CAST() Function – Definition and Syntax

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.

CONVERT() Function – Definition and Syntax

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))

PARSE() Function – Definition and Syntax

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

Comparison of CAST, CONVERT and PARSE Functions

The table below shows an overview of the differences between these three functions:

CAST Usage

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 Usage

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.

PARSE Usage

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 in SQL Server

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

Example of 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

Conclusion

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.