Скачать или смотреть Converting YYMMDDhhmm NVARCHAR to YYYY-MM-DD hh:mm:ss DATETIME in SQL Server

Learn to efficiently convert `YYMMDDhhmm` formatted NVARCHAR values into `YYYY-MM-DD hh:mm:ss` formatted DATETIME in SQL Server through precise string manipulation techniques.
---
This video is based on the question https://stackoverflow.com/q/74972080/ asked by the user 'K H A N' ( https://stackoverflow.com/u/2044776/ ) and on the answer https://stackoverflow.com/a/74972210/ provided by the user 'John Cappelletti' ( https://stackoverflow.com/u/1570000/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to convert YYMMDDhhmm formatted NVARCHAR into YYYY-MM-DD hh:mm:ss formatted DATETIME?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Converting YYMMDDhhmm NVARCHAR to YYYY-MM-DD hh:mm:ss DATETIME in SQL Server

When working with SQL Server, you may encounter date and time values formatted as YYMMDDhhmm, stored as NVARCHAR. This representation can be challenging when attempting to convert it into a more standard YYYY-MM-DD hh:mm:ss DATETIME format, which is essential for proper data handling and analysis. In this guide, we will break down the solution to convert these strings into the desired datetime format seamlessly.

The Challenge

Imagine you have a date-time string like 2208111603. The goal is to convert this string into a DATETIME format, specifically 2022-08-11 16:03:00. However, you might encounter several issues, as seen below:

Arithmetic Overflow Error when casting to numeric and then to datetime.

Conversion Failed Errors when using the CONVERT function with various formats and styles.

Here’s how you can solve this problem effectively through string manipulation.

The Solution

To convert the YYMMDDhhmm formatted NVARCHAR into YYYY-MM-DD hh:mm:ss formatted DATETIME, we will use some string manipulation functions provided by SQL Server. Below are the steps and SQL code required for the conversion:

Step 1: Basic Conversion

We can start with a simple SQL snippet using the TRY_CONVERT function and STUFF function to manipulate the string.

[[See Video to Reveal this Text or Code Snippet]]

What this code does:

STUFF function: It places a space and colon in the necessary positions to separate the date and time components.

TRY_CONVERT function: It attempts to convert the result into DATETIME format.

Results

When you execute the above code, it will yield:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Handling Century Logic

In some cases, you may have date strings representing years in two different centuries. For example, the string 6408111603 would represent the year 1964 instead of 2064. We can handle this scenario by adding conditional logic to our SQL statement. Here's how you can achieve that:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

CASE statement: It checks the first two characters of the string. If the first two digits are greater than 50, it assumes the century is 19, otherwise it adds 20.

Final Output

When you run the above query with the century logic, you'll get the correct DATETIME output for any given string formatted as YYMMDDhhmm.

Conclusion

By utilizing SQL Server's string manipulation functions such as STUFF and TRY_CONVERT, you can effectively convert YYMMDDhhmm formatted NVARCHAR strings to the standard YYYY-MM-DD hh:mm:ss DATETIME format. Remember to consider century logic when dealing with different year representations to prevent misinterpretation of date values. These strategies will ensure that your date-time data remains accurate and usable for your SQL Server applications.

By mastering such techniques, you can enhance your database management proficiency and ensure accurate date-time operations in your SQL Server environment.