1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
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
