snippetModerate
Convert a date range to an interval description
Viewed 0 times
convertrangedescriptionintervaldate
Problem
A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, something like "1 year, 3 months to go."
The built-in
Returns the count ... of the specified datepart boundaries crossed between the specified startdate and enddate.
If used as-is this could produce misleading or confusing results. For example, using an interval of YEAR would show 1999-12-31 (YYYY-MM-DD) and 2000-01-01 to be one year apart whereas common sense would say these dates are separated by only 1 day. Conversely using an interval of DAY 1999-12-31 and 2010-12-31 are separated by 4,018 days while most people would see "11 years" as a better description.
Starting from the number of days and calculating months and years from there would be prone to leap year and size-of-month errors.
I got to wondering how this could be implemented in the various SQL dialects? Example output includes:
```
create table TestData(
FromDate date not null,
ToDate date not null,
ExpectedResult varchar(100) not null); -- exact formatting is unimportant
insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
('1999-12-31', '2000-01-01', '1 day'),
('2000-01-01', '2000-02-01', '1 month'),
('2000-02-01', '2000-03-01', '1 month'), -- month length not important
('2000-01-28', '2000-02-29', '1 month, 1 day'), -- leap years to be accounted for
('2000-01-01', '2000-12-31', '11 months, 30 days'),
('2000-02-28', '2000-03-01', '2 days'),
('2001-02-28', '2001-03-01', '1 day'), -- not a leap year
('2000-01-01', '2001-01-01', '1 year'),
('2000-01-01', '2011-01-01', '11 years'),
('9999-12-30', '9999-12-31', '1 day'), -- catch overflow in date calculations
('1900-01-01', '9999-12-31', '8099 years 11 month
The built-in
DATEDIFF function Returns the count ... of the specified datepart boundaries crossed between the specified startdate and enddate.
If used as-is this could produce misleading or confusing results. For example, using an interval of YEAR would show 1999-12-31 (YYYY-MM-DD) and 2000-01-01 to be one year apart whereas common sense would say these dates are separated by only 1 day. Conversely using an interval of DAY 1999-12-31 and 2010-12-31 are separated by 4,018 days while most people would see "11 years" as a better description.
Starting from the number of days and calculating months and years from there would be prone to leap year and size-of-month errors.
I got to wondering how this could be implemented in the various SQL dialects? Example output includes:
```
create table TestData(
FromDate date not null,
ToDate date not null,
ExpectedResult varchar(100) not null); -- exact formatting is unimportant
insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
('1999-12-31', '2000-01-01', '1 day'),
('2000-01-01', '2000-02-01', '1 month'),
('2000-02-01', '2000-03-01', '1 month'), -- month length not important
('2000-01-28', '2000-02-29', '1 month, 1 day'), -- leap years to be accounted for
('2000-01-01', '2000-12-31', '11 months, 30 days'),
('2000-02-28', '2000-03-01', '2 days'),
('2001-02-28', '2001-03-01', '1 day'), -- not a leap year
('2000-01-01', '2001-01-01', '1 year'),
('2000-01-01', '2011-01-01', '11 years'),
('9999-12-30', '9999-12-31', '1 day'), -- catch overflow in date calculations
('1900-01-01', '9999-12-31', '8099 years 11 month
Solution
This answer shows an implementation using a SQL Server (2005+) CLR function.
Assembly and function
```
CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11134570000000000000000E00002210B010B00000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B42900005700000000400000A802000000000000000000000000000000000000006000000C0000007C2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000F0290000000000004800000002000500EC210000900600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003008601000001000011020A0F01280600000A0F00280600000A590B160C160D072C5C0F00280700000A0F01280700000A30200F00280700000A0F01280700000A33140F00280800000A0F01280800000A31040717590B120007280900000A0A2B1D120017280A00000A03280B00000A2C5B0817580C120017280A00000A0A0603280C00000A2C451200280600000A7E0D00000A13051205280600000A33C31200280700000A7E0D00000A13061206280700000A33AC2B150917580D120023000000000000F03F280E00000A0A0603280C00000A2DE21F64730F00000A13040716313D1104076F1000000A26110407172E0772010000702B05720F0000706F1100000A2611040816300B091630077E1200000A2B05721B0000706F1100000A26081631391104086F1000000A26110408172E0772210000702B0572310000706F1100000A261104091630077E1200000A2B05721B0000706F1100000A2609163006072D24082D211104096F1000000A26110409172E07723F0000702B05724B0000706F1100000A2611046F1300000A2A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E000014020000F001000023537472696E6773000000000404000058000000235553005C0400001000000023475549440000006C0400002402000023426C6F620000000000000002000001471502000900000000FA253300160000010000000A000000020000000200000003000000140000000500000001000000010000000200000000000A0001000000000006003D0036000600440036000A008E0073000600BB00A8001300CF0000000600FE00DE0006001E01DE000A00460173000600C501B9010600DA0136000000000001000000000001000100010010001800000005000100010050200000000096004D000A000100E22100000000861861001200040000000000000000000100A00000000200A500190061001200210061004800310061004E0039006100120041006100120011005B01B60111006401B60111006E01B60111007601BA0111007F01BA0111008901C00111009C01C0011100A801C8011100B101CC01490061004E004900D301D2014900D301D8015100E101DE010900E701E10109006100120020002B00530024000B0016002E001300F3012E001B00FC012E0023000502E5010480000000000000000000000000000000003C01000002000000000000000000000001002D000000000002000000000000000000000001006700000000000000003C4D6F64756C653E004461746162617365312E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A656374004461746554696D6500496E74657276616C4465736372697074696F6E002E63746F720053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650046726F6D00546F0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053716C46756E6374696F6E417474726962757465006765745F59656172006765745F4D6F6E7468006765745F446179004164645965617273004164644D6F6E746873006F705F4C6573735468616E4F72457175616C006F705F4C6573735468616E004D617856616C756500416464446179730053797374656D2E5465787400537472696E674275696C64657200417070656E6400537472696E6700456D70747900546F537472696E6700000D200079006500610072007300000B2000790065006100720000052C002000000F20006D006F006E00740068007300000D20006D006F006E0074006800000B200064006100790073000009200064006100790000000000AFDAAB526E833740886DDFF9139712E60008B77A5C561934E0890700020E1109110903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A656400000005200101111504200101088161010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657
-- Enable CLR (if necessary)
EXECUTE sys.sp_configure
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;Assembly and function
```
CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11134570000000000000000E00002210B010B00000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B42900005700000000400000A802000000000000000000000000000000000000006000000C0000007C2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000F0290000000000004800000002000500EC210000900600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003008601000001000011020A0F01280600000A0F00280600000A590B160C160D072C5C0F00280700000A0F01280700000A30200F00280700000A0F01280700000A33140F00280800000A0F01280800000A31040717590B120007280900000A0A2B1D120017280A00000A03280B00000A2C5B0817580C120017280A00000A0A0603280C00000A2C451200280600000A7E0D00000A13051205280600000A33C31200280700000A7E0D00000A13061206280700000A33AC2B150917580D120023000000000000F03F280E00000A0A0603280C00000A2DE21F64730F00000A13040716313D1104076F1000000A26110407172E0772010000702B05720F0000706F1100000A2611040816300B091630077E1200000A2B05721B0000706F1100000A26081631391104086F1000000A26110408172E0772210000702B0572310000706F1100000A261104091630077E1200000A2B05721B0000706F1100000A2609163006072D24082D211104096F1000000A26110409172E07723F0000702B05724B0000706F1100000A2611046F1300000A2A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E000014020000F001000023537472696E6773000000000404000058000000235553005C0400001000000023475549440000006C0400002402000023426C6F620000000000000002000001471502000900000000FA253300160000010000000A000000020000000200000003000000140000000500000001000000010000000200000000000A0001000000000006003D0036000600440036000A008E0073000600BB00A8001300CF0000000600FE00DE0006001E01DE000A00460173000600C501B9010600DA0136000000000001000000000001000100010010001800000005000100010050200000000096004D000A000100E22100000000861861001200040000000000000000000100A00000000200A500190061001200210061004800310061004E0039006100120041006100120011005B01B60111006401B60111006E01B60111007601BA0111007F01BA0111008901C00111009C01C0011100A801C8011100B101CC01490061004E004900D301D2014900D301D8015100E101DE010900E701E10109006100120020002B00530024000B0016002E001300F3012E001B00FC012E0023000502E5010480000000000000000000000000000000003C01000002000000000000000000000001002D000000000002000000000000000000000001006700000000000000003C4D6F64756C653E004461746162617365312E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A656374004461746554696D6500496E74657276616C4465736372697074696F6E002E63746F720053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650046726F6D00546F0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053716C46756E6374696F6E417474726962757465006765745F59656172006765745F4D6F6E7468006765745F446179004164645965617273004164644D6F6E746873006F705F4C6573735468616E4F72457175616C006F705F4C6573735468616E004D617856616C756500416464446179730053797374656D2E5465787400537472696E674275696C64657200417070656E6400537472696E6700456D70747900546F537472696E6700000D200079006500610072007300000B2000790065006100720000052C002000000F20006D006F006E00740068007300000D20006D006F006E0074006800000B200064006100790073000009200064006100790000000000AFDAAB526E833740886DDFF9139712E60008B77A5C561934E0890700020E1109110903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A656400000005200101111504200101088161010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657
Code Snippets
-- Enable CLR (if necessary)
EXECUTE sys.sp_configure
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11134570000000000000000E00002210B010B00000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B42900005700000000400000A802000000000000000000000000000000000000006000000C0000007C2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000F0290000000000004800000002000500EC210000900600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003008601000001000011020A0F01280600000A0F00280600000A590B160C160D072C5C0F00280700000A0F01280700000A30200F00280700000A0F01280700000A33140F00280800000A0F01280800000A31040717590B120007280900000A0A2B1D120017280A00000A03280B00000A2C5B0817580C120017280A00000A0A0603280C00000A2C451200280600000A7E0D00000A13051205280600000A33C31200280700000A7E0D00000A13061206280700000A33AC2B150917580D120023000000000000F03F280E00000A0A0603280C00000A2DE21F64730F00000A13040716313D1104076F1000000A26110407172E0772010000702B05720F0000706F1100000A2611040816300B091630077E1200000A2B05721B0000706F1100000A26081631391104086F1000000A26110408172E0772210000702B0572310000706F1100000A261104091630077E1200000A2B05721B0000706F1100000A2609163006072D24082D211104096F1000000A26110409172E07723F0000702B05724B0000706F1100000A2611046F1300000A2A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E000014020000F001000023537472696E6773000000000404000058000000235553005C0400001000000023475549440000006C0400002402000023426C6F620000000000000002000001471502000900000000FA253300160000010000000A000000020000000200000003000000140000000500000001000000010000000200000000000A0001000000000006003D0036000600440036000A008E0073000600BB00A8001300CF0000000600FE00DE0006001E01DE000A00460173000600C501B9010600DA0136000000000001000000000001000100010010001800000005000100010050200000000096004D000A000100E22100000000861861001200040000000000000000000100A00000000200A500190061001200210061004800310061004E0039006100120041006100120011005B01B60111006401B60111006E01B60111007601BA0111007F01BA0111008901C00111009C01C0011100A801C8011100B101CC01490061004E004900D301D2014900D301D8015100E101DE010900E701E10109006100120020002B00530024000B0016002E001300F3012E001B00FC012E0023000502E50104800000000SELECT
TD.FromDate,
TD.ToDate,
TD.ExpectedResult,
IntervalDescription = dbo.IntervalDescription(TD.FromDate, TD.ToDate)
FROM dbo.TestData AS TD;Context
StackExchange Database Administrators Q#138080, answer score: 11
Revisions (0)
No revisions yet.