How to convert String to Date in SQL Server and T-SQL? Example tutorial

While working with crude information, you may oftentimes confront date values put away as text. Changing these qualities over completely to a date information type is vital since dates might be more significant during examination. In SQL Server, switching a string over completely to date can be accomplished in various methodologies.  In this article, we will cover various techniques to convert String to Date in all databases. 

 

By and large, there are two sorts of information type changes:

• Implicit: where transformations are not apparent to the client; information type is changed while stacking information without utilizing any capability
  
  
• Explicit: where changes are apparent to the client and they are performed utilizing CAST or CONVERT capabilities or different apparatuses

In this article, we will clarify how a string for date change can be accomplished implicitly, or explicitly in SQL Server utilizing worked in capabilities like CAST(), TRY_CAST(), CONVERT(), TRY_CONVERT() and TRY_PARSE().

Note: Before we start, kindly note that a portion of the SQL statements utilized are insignificant from the information setting viewpoint and are simply used to make sense of the idea.

1. SQL Server: convert string to date implicitly

As referenced above, changing over an information type implicitly isn't noticeable to the client, as an illustration when you are contrasting two fields or values having various information types:

SELECT * FROM information_schema.columns where '1' = 1

In SQL Server, switching string over completely to date implicitly relies upon the string date design and the default language settings (local settings); If the date put away inside a string is in ISO designs: yyyyMMdd or yyyy-MM-ddTHH:mm:ss(.mmm), it tends to be changed over no matter what the territorial settings.

Else the date should have an upheld configuration or it will toss an exemption, as an illustration while working under the provincial settings "EN-US", in the event that we attempt to change over a string with dd/MM/yyyy design it will fall flat since it attempts to change over it as MM/dd/yyyy design which is upheld.

SELECT * FROM information_schema.columns where GETDATE() > '13/12/2019'

2. SQL Server: Convert string to date explicitly

The second methodology for changing over information types is the explicit transformation which is finished by utilizing a few capabilities or devices. In SQL Server, switching a string over completely to date explicitly can be accomplished utilizing CONVERT(). CAST() and PARSE() capabilities.

CAST()

CAST() is the most essential change capability given by SQL Server. This capability attempts to switch given esteem over completely to a predetermined data type (data type length must be determined).

SELECT CAST('12/01/2019' as date) as StringToDate , CAST(GETDATE() 

as VARCHAR(50)) as DateToString

Note that in SQL Server, switching a string over completely to date utilizing CAST() capability relies upon the language settings like implicit change, as we referenced in the past segment, so you can change over ISO designs or upheld designs by the ongoing language settings.

CONVERT()

CONVERT() capability is further developed than CAST() since the change style can be indicated. This capability takes 3 contentions:

• the ideal data type
• the information worth
• the style number (discretionary).

On the off chance that the style number isn't passed to the capability, it behaves like the CAST() capability. Yet, assuming the style contention is passed, it will attempt to change over the worth in light of that style. For instance, assuming that we attempt to change over "13/12/2019" worth to date without indicating the style number, it will come up short since it isn't upheld by the ongoing language setting:

SELECT CONVERT(DATETIME,'13/12/2019')

PARSE()

PARSE() is SQL CLR capability that utilization .Net system Parse() capability. PARSE() syntax is as per the following:

PARSE(<value> AS <data type> [USING <culture>])

On the off chance that the way of life data isn't indicated, PARSE() acts like CAST() capability, however when the way of life is passed inside the articulation, the capability attempts to change the worth over completely to the ideal data type utilizing this culture. 

For instance, assuming that we attempt to parse 13/12/2019 worth without passing the way of life data, it will come up short since "dd/MM/yyyy" isn't upheld by the default language settings.

TRY_CAST(), TRY_CONVERT() and TRY_PARSE()

One of the main pressing concerns of the data type change capabilities is that they can't deal with the mistaken worth. For instance, commonly you might confront terrible date values, for example, "01/01/0000"; these qualities can't be changed over and will toss a data transformation exemption.

To settle this issue, you can utilize TRY_CAST(), TRY_CONVERT() or TRY_PARSE() capabilities to check in the event that the worth can be changed over or not, provided that this is true, the capability will return the transformation result, else it will return a NULL worth.

SELECT TRY_CAST('01/01/2000' as date), TRY_CAST('01/01/0000' as date)

SQL Server Convert String to Date dd/mm/yyyy

Presently to switch a string over completely to a dd/mm/yyyy formatted date, first, we need to change the string over completely to a standard date design. And afterward, we can utilize the convert() function to arrange the date as indicated by our prerequisites.

For transformation, we can utilize the accompanying query.

SELECT '16 Jun 2021' AS 'String', CONVERT(varchar(10), CAST('16 Jun 2021' 

as date), 103) AS [dd/mm/yyyy]

In the above query, we are utilizing the cast() capability inside the convert() capability to change the string to a date design. And afterward, we are utilizing the formatted date result inside the Convert() capability to come by the ideal outcome. To obtain the ideal outcome, we are utilizing the 103 style code.

SQL Server Convert String to Date yyyymmdd

Sadly, there is no immediate way through which we can change a string over completely to yyyymmdd date design in SQL Server. For transformation, we need to initially switch the string over completely to a standard date configuration, and afterward we need to change the standard date over completely to a varchar data type.

Also, by utilizing the Convert() capability, we can arrange it to yyyymmdd design.

SELECT '16 Jun 2021' AS 'String', CONVERT(varchar(10), CAST('16 Jun 2021' 

as date), 112) AS [yyyymmdd]

In the query above, first, we are utilizing the cast() capability inside the convert() capability to change the string to a date design. And afterward, we are utilizing the new date result inside the convert() capability to get the expected arrangement result. To come by the necessary outcome, we are utilizing the 112 style code. Eventually, it will return the accompanying result.

Conclusion

In this article, we made sense of data transformation approaches overall. Then we showed how, while utilizing SQL Server, changing a string over completely to date can be accomplished utilizing these methodologies. We made sense of the framework capabilities given by SQL Server by giving a few models and outer connections that give more subtleties.

Other SQL and Database Articles you may like:

• How to find top 10 records in a table?
• How to use Stored Procedure in SQL?
• 10 Examples of ALTER command in SQL
• Difference between UNION and UNION ALL in SQL
• What are T-SQL Window Functions?
• How to use WHERE and HAVING clause in SQL
• 4 Examples of CASE expression in SQL Server
• MySQL vs NoSQL comparison
• Difference between Self and Equi join in SQL