USE [VEMZSQL]
GO
/****** Object:  UserDefinedFunction [dbo].[Format]    Script Date: 22.06.2020 15:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: Prostoysoft.ru
-- Create date: 2019-12-28
-- Description: patial analog of Access and VB 'Format' function for datetime

ALTER FUNCTION [dbo].[Format](@Date datetime, @Format varchar(50) = 'yyyy-MM-dd')
RETURNS varchar(50)
AS
BEGIN
	DECLARE @Result varchar(50)
	DECLARE @Year varchar(50)
	DECLARE @Month varchar(50)
	DECLARE @Day varchar(50)
	DECLARE @Hour varchar(50)
	DECLARE @Minute varchar(50)
	DECLARE @Second varchar(50)
	DECLARE @WeekDay varchar(50)
	DECLARE @Quarter varchar(50)
	DECLARE @MonthName varchar(50)

	SELECT @Year = YEAR(@Date)
	SELECT @Month = Month(@Date)
	SELECT @MonthName = CASE WHEN @Month=1 THEN 'Январь' WHEN @Month=2 THEN 'Февраль' WHEN @Month=3 THEN 'Март' WHEN @Month=4 THEN 'Апрель' WHEN @Month=5 THEN 'Май' WHEN @Month=6 THEN 'Июнь' WHEN @Month=7 THEN 'Июль' WHEN @Month=8 THEN 'Август' WHEN @Month=9 THEN 'Сентябрь' WHEN @Month=10 THEN 'Октябрь' WHEN @Month=11 THEN 'Ноябрь' WHEN @Month=12 THEN 'Декабрь' END
	SELECT @Day = Day(@Date)
	SELECT @Hour = DATEPART(hour, @Date)
	SELECT @Minute = DATEPART(minute, @Date)
	SELECT @Second = DATEPART(second, @Date)
	SELECT @Quarter = DATEPART(QUARTER, @Date)

	IF @Format Like 'hh_mm'
	BEGIN
		SELECT @Result = @Format
		SELECT @Result = REPLACE(@Result, 'hh', RIGHT('0' + @Hour, 2))
		SELECT @Result = REPLACE(@Result, 'mm', RIGHT('0' + @Minute, 2))
		SELECT @Result = REPLACE(@Result, 'ss', RIGHT('0' + @Second, 2))
		RETURN @Result
	END

	IF @Format Like 'hh_mm_ss'
	BEGIN
		SELECT @Result = @Format
		SELECT @Result = REPLACE(@Result, 'hh', RIGHT('0' + @Hour, 2))
		SELECT @Result = REPLACE(@Result, 'mm', RIGHT('0' + @Minute, 2))
		SELECT @Result = REPLACE(@Result, 'ss', RIGHT('0' + @Second, 2))
		RETURN @Result
	END

	IF LEN(@Format) = 16 --yyyy_MM_dd_hh_mm
	BEGIN
		SELECT @Result = LEFT(@Format, 10)
		SELECT @Result = REPLACE(@Result, 'MM', RIGHT('0' + @Month, 2))
		SELECT @Result = @Result + RIGHT(@Format, 6)
		SELECT @Result = REPLACE(@Result, 'mm', RIGHT('0' + @Minute, 2))
		SELECT @Result = REPLACE(@Result, 'yyyy', @Year)
		SELECT @Result = REPLACE(@Result, 'dd', RIGHT('0' + @Day, 2))
		SELECT @Result = REPLACE(@Result, 'hh', RIGHT('0' + @Hour, 2))
		SELECT @Result = REPLACE(@Result, 'ss', RIGHT('0' + @Second, 2))
		RETURN @Result
	END

	IF LEN(@Format) = 19 --yyyy_MM_dd_hh_mm_ss
	BEGIN
		SELECT @Result = LEFT(@Format, 10)
		SELECT @Result = REPLACE(@Result, 'MM', RIGHT('0' + @Month, 2))
		SELECT @Result = @Result + RIGHT(@Format, 9)
		SELECT @Result = REPLACE(@Result, 'mm', RIGHT('0' + @Minute, 2))
		SELECT @Result = REPLACE(@Result, 'yyyy', @Year)
		SELECT @Result = REPLACE(@Result, 'dd', RIGHT('0' + @Day, 2))
		SELECT @Result = REPLACE(@Result, 'hh', RIGHT('0' + @Hour, 2))
		SELECT @Result = REPLACE(@Result, 'ss', RIGHT('0' + @Second, 2))
		RETURN @Result
	END

	IF UPPER(@Format) LIKE '__________, DDD' --dd.MM.yyyy, DDD
	BEGIN
		SELECT @WeekDay = DATEPART(WEEKDAY, @Date) + DATEPART(WEEKDAY, 0) % 7
		SELECT @Result = LEFT(@Format, 10)
		SELECT @Result = REPLACE(@Result, 'dd', RIGHT('0' + @Day, 2))
		SELECT @Result = REPLACE(@Result, 'MM', RIGHT('0' + @Month, 2))
		SELECT @Result = REPLACE(@Result, 'yyyy', @Year)
		SELECT @WeekDay = CASE WHEN @WeekDay=1 THEN 'Вс' WHEN @WeekDay=2 THEN 'Пн' WHEN @WeekDay=3 THEN 'Вт' WHEN @WeekDay=4 THEN 'Ср' WHEN @WeekDay=5 THEN 'Чт' WHEN @WeekDay=6 THEN 'Пт' WHEN @WeekDay=7 THEN 'Сб' END
		SELECT @Result = @Result + ', ' + @WeekDay
		RETURN @Result

	END


	IF UPPER(@Format) LIKE 'MMMM[. ]YYYY' --MMMM yyyy
	BEGIN
		SELECT @Result = @Format
		SELECT @Result = REPLACE(@Result, 'MMMM', @MonthName)
		SELECT @Result = REPLACE(@Result, 'yyyy', @Year)
		RETURN @Result

	END


	IF LEN(@Format) <= 10
	BEGIN
		SELECT @Result = @Format
		SELECT @Result = REPLACE(@Result, 'yyyy', @Year)
		SELECT @Result = REPLACE(@Result, 'yy', @Year)
		SELECT @Result = REPLACE(@Result, 'MMMM', @MonthName)
		SELECT @Result = REPLACE(@Result, 'MM', RIGHT('0' + @Month, 2))
		SELECT @Result = REPLACE(@Result, 'dd', RIGHT('0' + @Day, 2))
		RETURN @Result
	END

	SELECT @Result = REPLACE(@Result, 'q', @Quarter)

	RETURN @Result
END