Скачать или смотреть How to Add Leading Zeros and Swap Date Formats in SQL Server Integers

Learn how to fix improperly formatted integer dates in SQL Server by adding leading zeros and converting MMDDYYYY format to YYYYDDMM efficiently.
---
This video is based on the question https://stackoverflow.com/q/79356368/ asked by the user 'Thatguywes' ( https://stackoverflow.com/u/22027321/ ) and on the answer https://stackoverflow.com/a/79356547/ provided by the user 'Joel Coehoorn' ( https://stackoverflow.com/u/3043/ ) 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: Add leading 0 and also swap MMDDYYYY INT to YYYYDDMM INT

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 drop me a comment under this video.
---
Introduction

When working with date data stored as integers in SQL Server, you might encounter improperly formatted values like 7312018 instead of 07312018. The challenge is adding a leading zero and then converting the date format from MMDDYYYY to YYYYDDMM.

Understanding the Problem

Input date: an integer like 7312018 (7 digits instead of 8).

Desired output: integer with format YYYYDDMM, e.g., 20180731.

The initial approach is to convert the integer to a string, pad it with a leading zero, and then rearrange. However, working with integers for this manipulation has limitations and can be inefficient.

Key Considerations

Integers have no leading zeros. Any leading zero is lost once the value is stored as an integer.

Such formatting issues should preferably be handled in the client/reporting layer, not in the database.

Best Practice: Use SQL Server Date Types

If possible, convert the integer dates into proper DATE types. This allows for:

Accurate date representation and validation.

Flexible formatting during output.

Using the DATEFROMPARTS() function, you can parse the integer into a date:

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

Once converted, store or manipulate dates using the DATE data type.

Quick Integer-Based Solution

If you must keep the values as integers and convert MMDDYYYY to YYYYDDMM without string operations, use simple arithmetic:

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

Explanation:

% 10000 extracts the YYYY part (last 4 digits).

/ 10000 extracts the MMDD part (first 4 digits).

Multiplying the year by 10,000 shifts it left, then adding the original month and day bits completes the swap.

This method:

Handles both 7- and 8-digit inputs correctly.

Is efficient and avoids string conversions.

Summary

Prefer converting dates to SQL Server's DATE type for accuracy and ease.

If integer format must be maintained, use arithmetic to rearrange date parts instead of string manipulation.

Remember integers cannot preserve leading zeros, so handle formatting on output rather than in storage.

By applying these approaches, you can clean up date data and convert formats cleanly within SQL Server.