Скачать или смотреть Convert varchar Date in SQL Server from '19-09-2020' to '2020-09-19'

Learn how to easily convert a date stored as `varchar` in SQL Server from '19-09-2020' to '2020-09-19' using the correct SQL queries.
---
This video is based on the question https://stackoverflow.com/q/63966635/ asked by the user 'Muzaffar' ( https://stackoverflow.com/u/7029150/ ) and on the answer https://stackoverflow.com/a/63966729/ provided by the user 'dnoeth' ( https://stackoverflow.com/u/2527905/ ) 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: I have date stored in varchar in SQL Server as '19-09-2020' and I want to convert it to '2020-09-19'

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 Date Format in SQL Server: From varchar to date

Storing dates in a format that SQL Server can easily understand is crucial for maintaining the integrity of your data and for performing date-related operations. If you've encountered a situation where a date is stored as a varchar (for instance, '19-09-2020') and you need to convert it to a more standardized format ('2020-09-19'), you've come to the right place.

In this guide, we will explain how to perform this conversion efficiently using SQL queries.

Understanding the Problem

You have a date stored in a column formatted as a varchar type in SQL Server, and it looks like this:

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

Your goal is to convert this value into:

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

This requirement often arises when handling data stored in text formats, especially when dealing with databases that require consistent date representations.

The Solution

Step 1: Specifying the Source Format

When converting dates in SQL Server, it's important to specify the original format of the date string you are dealing with. In this case, the given date 19-09-2020 is in the format DD-MM-YYYY. To convert it properly, we will use the CONVERT function with the appropriate style code.

Here is how you do it:

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

Explanation of Variables:

CONVERT: A function to convert one data type to another.

date: The target data type you want the value to be converted to.

'19-09-2020': The source value you want to convert.

105: The style code that indicates the format of the provided date (in this case, DD-MM-YYYY).

After this conversion, you will get a date value without quotes, formatted internally as a SQL date.

Step 2: Converting Back to varchar (If Necessary)

While it's often not necessary to convert the date back into a varchar, there may be scenarios where you need the final output as a string. To convert the date back into the varchar format YYYY-MM-DD, you can use the following SQL command:

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

Explanation of Inner CONVERT:

This first converts the varchar date to a date type as explained previously.

Explanation of Outer CONVERT:

The outer CONVERT changes the date back into a varchar, and 23 represents the format YYYY-MM-DD.

Key Takeaways

Always specify the correct format when converting dates in SQL Server.

For date stored as varchar, use style code 105 for DD-MM-YYYY.

Conversion back to varchar can be done while specifying the desired output format.

By following these steps, you can confidently transform date strings in SQL Server, ensuring that your data maintains its integrity and is compatible with database operations.

Whether dealing with imported data or legacy systems, mastering date conversions can save you valuable time and prevent data-related errors in your applications.

If you have any questions or need further assistance, feel free to reach out. Happy querying!