aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN
end_date	date	IN
format	integer	IN (DEFAULT 1)

Definition

WITH D AS (
		SELECT --extract(month from start_date) AS start_month,
--			   extract(year from start_date) AS start_year,	
--			   extract(month from end_date) AS end_month, 
--			   extract(year from end_date) AS end_year,	
--			   extract(month from CURRENT_DATE) AS cur_month, 
--			   extract(year from CURRENT_DATE) AS cur_year,
			   extract(day from start_date) AS start_day,	
			   extract(day from end_date) AS end_day,
			   to_char(start_date, 'Mon') AS start_month,
			   to_char(end_date, 'Mon') AS end_month,
			   extract(year from start_date) AS start_year,	
			   extract(year from end_date) AS end_year,
			   (extract(month from start_date) = extract(month from end_date)) AS is_same_month,
			   (extract(year from start_date) = extract(year from end_date)) AS is_same_year,
			   (extract(year from start_date) = extract(year from CURRENT_DATE)) AS is_current_year  
	)
	SELECT 
		case 
			when (is_same_year AND is_current_year AND is_same_month) 
				then format('%s - %s %s', start_day, end_day, end_month) 
				--start_day || ' - ' || end_day || ' ' ||end_month
			when (is_same_year AND is_current_year AND NOT is_same_month)
				then format('%s %s - %s %s', start_day, start_month, end_day, end_month) 
				--start_day ||' '|| start_month || ' - ' || end_day ||' '|| end_month
			when (is_same_year AND is_same_month) 
				then format('%s - %s %s %s', start_day, end_day, end_month, end_year) 
				--to_char(start_date, 'DD') || ' - ' || to_char(end_date, 'DD') || to_char(end_date, ' Mon YYYY')
			when (is_same_year AND NOT is_same_month)
				then format('%s %s - %s %s %s', start_day, start_month, end_day, end_month, end_year) 
				--to_char(start_date, 'DD Mon') || ' - ' || to_char(end_date, 'DD Mon') || to_char(end_date, ' YYYY')
			else 
				format('%s %s %s - %s %s %s', start_day, start_month, start_year, end_day, end_month, end_year) 
				--to_char(start_date, 'DD Mon YYYY') || ' - ' || to_char(end_date, 'DD Mon YYYY')						
		END
	FROM D

fn_period_to_str

Parameters

Definition