Скачать или смотреть Converting Integers to Datetime in SQL Server

Learn how to easily convert an `int` to display as `datetime` in SQL Server, especially when working with date and time data formats.
---
This video is based on the question https://stackoverflow.com/q/65568382/ asked by the user 'Jason312' ( https://stackoverflow.com/u/7432394/ ) and on the answer https://stackoverflow.com/a/65568426/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) 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 an int to display as 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 Integers to Datetime in SQL Server: A Simple Guide

If you work with databases, you may often encounter the need to convert integers into more user-friendly formats, such as datetime. This is particularly true in SQL Server, where integers might represent date and time information. For instance, you might come across a numeric value like 201061311912, which encodes the date and time but isn't easily readable. This guide will guide you through the process of converting such integers into the datetime format using SQL Server Management Studio (SSMS).

Understanding the Problem

Suppose you have an integer string, such as:

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

At first glance, this might look confusing, but it contains encoded datetime information. Specifically, it represents:

Year: 2010

Month: 06 (June)

Day: 13 (13th day)

Hour: 11

Minutes: 19

Seconds: 12

To display this integer as a readable datetime in SQL Server, we need to break it down into its components and then reassemble it into a proper datetime format.

Solution: Converting Integer to Datetime

To facilitate the conversion, SQL Server provides a handy function called datetimefromparts(), which we can use to construct a datetime value from separate year, month, day, hour, minute, and second parts.

Step-By-Step Breakdown

Here’s how you can achieve the conversion using SQL queries:

Using the datetimefromparts() function:
This function takes individual components (year, month, day, hour, minute, and second) and combines them into a single datetime value.

Extracting the Components:
We can use string functions to slice the integer into its respective components. Here’s the SQL query that does exactly this:

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

Explanation of the Query

LEFT(str, 4): This extracts the first four characters from the left of the string, giving us the year.

SUBSTRING(str, 5, 2): This extracts the two characters starting from the fifth position, which represents the month.

SUBSTRING(str, 7, 2): This extracts the two characters starting from the seventh position, representing the day.

SUBSTRING(str, 9, 2): Similarly, this gets the hours.

SUBSTRING(str, 11, 2): This extracts the minutes.

SUBSTRING(str, 13, 2): This extracts the seconds.

Each of these extracted values is then passed into the datetimefromparts() function to return a properly formatted datetime value.

Conclusion

By using the datetimefromparts() function in SQL Server, you can effortlessly convert integers that represent datetime information into a readable format. Whether you're dealing with large datasets or just a few entries, understanding how to manipulate and convert your data ensures that you can present it clearly and effectively.

Feel free to adapt this approach to suit your specific data formats or business needs!