Programs

SQL_TABLE_VALUED_FUNCTION - fun_date_array

Main program

Name	fun_date_array
Type_desc	SQL_TABLE_VALUED_FUNCTION
Comment	SQLServer User-Defined Function PURPOSE Return dates of every given interval on current given period. This function can be combined with function fun_time_array. It is possible to get an array with dates + time interval. See example with CROSS APPLY dbo.fun_time_array PARAMETERS @interval (VARCHAR(20)) can be: 'Decade', 'de' means every Decade 'Year', 'yy' or 'yyyy' means every Year 'Quarter','qq' or 'q' means every Quarter 'Month','mm' or'm' means every Month 'Week', 'wk', or 'ww' means every Week 'Day','dd' or'd' means every Day all other means every Day @period (VARCHAR(20)) can be: 'Century', 'ce' means first day until last day of current Century (valid for interval Decade, Year, Quarter, Month, Week or Day) 'Decade', 'de' means first day until last day of current Decade (valid for interval Year, Quarter, Month, Week or Day) 'Year', 'yy' or 'yyyy' means first day until last day of current Year (valid for interval Quarter, Month, Week or Day) 'Quarter','qq' or 'q' means first day until last day of current Quarter (valid for interval Month, Week or Day) 'Month','mm' or'm' means first day until last day of current Month (valid for interval Week or Day) 'Week', 'wk', or 'ww' means first day until last day of current Week (valid for interval Day) all other means first day until last day of current Decade (for interval Year), current Century (for interval Decade), current Year (for every other interval) @array (value DATETIME) is output table. Timepart is 00:00:00:000. EXAMPLES SELECT * FROM dbo.fun_date_array('Decade', 'Century'); SELECT * FROM dbo.fun_date_array('Year', 'Century'); SELECT * FROM dbo.fun_date_array('Year', 'Decade'); SELECT * FROM dbo.fun_date_array('Quarter', 'Decade'); SELECT * FROM dbo.fun_date_array('Quarter', 'Year'); SELECT * FROM dbo.fun_date_array('Month', 'Year'); SELECT * FROM dbo.fun_date_array('Week', 'Month'); SELECT * FROM dbo.fun_date_array('Day', 'Quarter'); SELECT * FROM dbo.fun_date_array('Day', 'Month'); SELECT * FROM dbo.fun_date_array('Day', 'Week'); -- Give an array with every hour per day for all dates in current month: SELECT t2.value FROM dbo.fun_date_array('Day', 'Month') t1 CROSS APPLY dbo.fun_time_array(t1.value,'Hour', 1) t2; HISTORY 2015-12-02 - Created function 2016-10-13 - Added an example of CROSS APPLY with function fun_time_array 2017-11-08 - Added century and decade as possible period 2017-11-08 - Bug fixed: enddate was missing in list 2024-02-09 - Changed history table TAGS <program> <description>Return dates of every given interval on current given period</description> <generic>1</generic> <author>Gerrit Mantel</author> <created>2015-12-02</created> <lastmodified>2024-02-09</lastmodified> </program>
Description	Return dates of every given interval on current given period
Minversion
Generic	1
Author	Gerrit Mantel
Created	2015-12-02 00:00:00
Lastmodified	2024-02-09 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	0
Sourcecode