SQLServer generation time range; sqlserver generation range
I wrote a similar article earlier, which is used in development.
If OBJECT_ID (n'tf _ Data_TimeRange ', n'fn') is not nullddrop function tf_Data_TimeRangegocreate function tf_Data_TimeRange (@ startDate varchar (20), -- start date @ endDate varchar (20 ), -- end date @ dataType int -- Data Type 1: hour 2: Day) returns @ temp table (orderby int, MonitorTime varchar (20 )) as/******************************** -- function: recursive Generation Time Period -- author: zhujt -- create date: 17:07:11 **********************************/beginif @ dataType = 1 beginwith temp (orderby, vdate) as (select 1 orderby, convert (varchar (20), @ startDate, 120) union all select orderby + 1, convert (varchar (20), dateadd (HOUR, 1, vdate), 120) from temp where vdate <@ endDate) insert into @ temp (orderby, MonitorTime) select orderby, vdate from tempOPTION (MAXRECURSION 0) -- exclude limit endelse if @ dataType = 2 begin set @ endDate = convert (varchar (10), @ endDate, 120); with temp (orderby, vdate) as (select 1 orderby, convert (varchar (10), @ startDate, 120) union all select orderby + 1, convert (varchar (10), dateadd (DD, 1, vdate), 120) from temp where vdate <@ endDate) insert into @ temp (orderby, MonitorTime) select orderby, vdate from tempOPTION (MAXRECURSION 0) -- exclude limit end return; end