Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people don't know what to do about how to customize the split month function in sql server. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Why is there a need for a custom partition month

Today, when combing through all the functions of a platform, we found a custom partition month function, that is, the index value of the start day of the specified partition month (which can be obtained from any value in the closed range of 1-31) to obtain the partition month value corresponding to the specified date. At that time, this function is used to solve the problem that the business unit obtains the statistical summary data of the non-standard month (the standard month is a completed standard month from the first day to the last day of each month). For example, if the index value of the start day of the specified partition month is 5, it means that it is a complete partition month between the 5th of a month and the 4th of the next month; similarly, if the index value of the start day of the specified partition month is 1, it represents the standard month, and so on.

I carefully combed this function for refactoring, simplification and expansion, and the implementation of the custom segmentation monthly function differs from the SQL Server time granularity series written before-Section 3 ten-day, monthly time granularity detailed explanation article will be an integer value and month date conversion function, this is implemented according to the standard month, although the idea is roughly the same However, instead of extending the previous month date and integer value conversion function pairs, new functions are developed independently. It is also to achieve the singleness, stability, maintainability and expansibility of the function as far as possible.

2. Sql server realizes the function of custom segmentation month.

The custom split month function includes two scalar functions: ufn_SegMonths and ufn_SegMonth3Date. Ufn_SegMonths gets the split month value corresponding to the specified date in the custom split month; ufn_SegMonth3Date gets the month date corresponding to the specified split month value.

The implementation T-SQL code for the sql server version is as follows:

IF OBJECT_ID (N' [dbo]. [ufn_SegMonths]', 'FN') IS NOT NULLBEGIN DROP FUNCTION [dbo]. [ufn_SegMonths]; ENDGO-- =-- function: gets the custom number of months on a specified date based on the custom month start index value. -- description: custom split months = annual integral value * 100 + current partition month value. Environment: SQL Server 2005 +. -- call: SET @ intSegMonths = dbo.fn_SegMonths ('2008-01-14, 15). -- create: XXXX-XX-XX XX:XX-XX:XX XXX creates a function implementation. -- modify: XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX. -- = = CREATE FUNCTION [dbo]. [ufn_SegMonths] (@ dtmDate AS DATETIME-- date, @ tntSegStartIndexOfMonth AS INT = 15-- Custom split month start index value (1-31)) RETURNS INTASBEGIN IF (@ tntSegStartIndexOfMonth = 0 OR @ tntSegStartIndexOfMonth > = 32) BEGIN SET @ tntSegStartIndexOfMonth = 15; END DECLARE @ intYears AS INT, @ tntMonth AS TINYINT, @ sntDay AS SMALLINT SELECT @ intYears = DATEDIFF (YEAR, '1900-01-01-01, @ dtmDate), @ tntMonth = DATEPART (MONTH, @ dtmDate), @ sntDay = DATEPART (DAY, @ dtmDate); IF (@ sntDay > = @ tntSegStartIndexOfMonth) BEGIN SET @ tntMonth = @ tntMonth + 1; END IF (@ tntMonth > 12) BEGIN SELECT @ intYears = @ intYears + 1, @ tntMonth = @ tntMonth-12; END RETURN @ intYears * 100 + @ tntMonth ENDGO IF OBJECT_ID (N' [dbo]. [ufn_SegMonths2Date]', 'FN') IS NOT NULLBEGIN DROP FUNCTION [dbo]. [ufn_SegMonths2Date]; ENDGO-- =-- function: get the custom split month date corresponding to the custom split month. -- description: custom split month date = the number of custom split months / 100 corresponding to the year integer date "combination" the current split month value. Environment: SQL Server 2005 +. Call: SET @ dtmSegMonthDate = dbo.fn_SegMonths2Date (11602). -- create: XXXX-XX-XX XX:XX-XX:XX XXX creates a function implementation. -- modify: XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX. ;-- = CREATE FUNCTION [dbo]. [ufn_SegMonths2Date] (@ intSegMonths AS INT-- Custom split months) RETURNS DATETIMEASBEGIN DECLARE @ dtmDefaultBasedate AS DATETIME; SET @ dtmDefaultBasedate = '1900-01-01-01; IF ((@ intSegMonths IS NULL) OR (@ intSegMonths = 27GO) after reading the above, have you mastered the method of how to customize the split month function in sql server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.