/***************************************************************************************************
# DATE AND TIME MANIPULATION ROUTINES:
# --------------------------------------------------------------------------------------------------
# SQLAUX scripting library | Author: Sergei Y.Kitáev
# -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
*/

/*==================================================================================================
# DATE FORMAT IDENTIFIER (IN CORRESPONCENCE TO SQL LOCALE, OR WITH ITS OVERRIDING):
# === Usage: ===
# dbo.[AUX:DateFormat]()
*/
function [AUX:DateFormat]()
returns int
/*==================================================================================================
# TIME FORMAT IDENTIFIER (IN CORRESPONCENCE TO SQL LOCALE, OR WITH ITS OVERRIDING):
# === Usage: ===
# dbo.[AUX:TimeFormat]()
*/
function [AUX:TimeFormat]()
returns int


/*==================================================================================================
# DATE COMPARISON, WITH ROUNDING TO “YMD” (VIA TRUNCATION OF TIME PARTS):
# Comparison result
# :   -1 —— 1st YMD is lesser than 2nd YMD;
# :    1 —— 1st YMD is greater than 2nd YMD;
# :    0 —— 1st YMD is equal to 2nd YMD;
# : null —— correlation is undetermined (at least one argument is null)
# === Usage: ===
# dbo.[AUX:CompareDates]( <Date-1>, <Date-2> )
*/
function [AUX:CompareDates]( @Date_1 datetime2, @Date_2 datetime2 )
returns int
/*==================================================================================================
# DATE COMPARISON, WITH ROUNDING TO “YMD” (VIA TRUNCATION OF TIME PARTS):
# Comparison result
# :   -1 —— 1st YMD is lesser than 2nd YMD;
# :    1 —— 1st YMD is greater than 2nd YMD;
# :    0 —— 1st YMD is equal to 2nd YMD;
# : null —— correlation is undetermined (null arg(s) or incorrect YMD2)
# === Usage: ===
# dbo.[AUX:CompareDate.YMD]( <Date-1>, <Year-2>, <Month-2>, <Day-2> )
# dbo.[AUX:CompareDate.YMD]( <Date-1>, <Year-2>, <Month-2>, null )
# dbo.[AUX:CompareDate.YMD]( <Date-1>, <Year-2>, null, null )
*/
function [AUX:CompareDate.YMD]( @Date_1 datetime2,
                                @Year_2 int, @Month_2 int=null, @Day_2 int=null )
returns int

/*==================================================================================================
# COMPARISON OF TWO YEAR-MONTHS:
# Comparison result
# :   -1 —— 1st year-month is lesser than 2nd year-month;
# :    1 —— 1st year-month is greater than 2nd year-month;
# :    0 —— 1st year-month is equal to 2nd year-month;
# : null —— correlation is undetermined (null arg(s) or incorrect YM)
# === Usage: ===
# dbo.[AUX:YMComparison]( <Year-1>, <Month-1>, <Year-2>, <Month-2> )
# dbo.[AUX:YMComparison]( <Year-1>, <Month-1>, <Year-2>, null )
# dbo.[AUX:YMComparison]( <Year-1>, null, <Year-2>, <Month-2> )
*/
function [AUX:YMComparison]( @Year_1 int, @Month_1 int=null,
                             @Year_2 int, @Month_2 int=null )
returns int
/*==================================================================================================
# COMPARISON OF TWO YEAR-MONTHS (WITH FIRST ARGUMENT OF “DATETIME” TYPE):
# Comparison result
# :   -1 —— 1st year-month is lesser than 2nd year-month;
# :    1 —— 1st year-month is greater than 2nd year-month;
# :    0 —— 1st year-month is equal to 2nd year-month;
# : null —— correlation is undetermined (null arg(s) or incorrect YM2)
# === Usage: ===
# dbo.[AUX:YMComparison.ByDate]( <Date-1>, <Year-2>, <Month-2> )
# dbo.[AUX:YMComparison.ByDate]( <Date-1>, <Year-2>, null )
*/
function [AUX:YMComparison.ByDate]( @Date_1 datetime2,
                                    @Year_2 int, @Month_2 int=null )
returns int


/*==================================================================================================
# MAKING PURE DATE VALUE (ONLY DAY, MONTH AND YEAR PARTS):
# === Usage: ===
# dbo.[AUX:PureDate]( <Date-time> )
*/
function [AUX:PureDate]( @DateTime datetime2 )
returns datetime2
/*==================================================================================================
# CHECKING DATE-TIME, THAT IT HAS NOT SMALL COMPONENTS (LESSER THAN DAY PART) —
# ONLY DAY, MONTH AND YEAR PARTS ARE ADMISSIBLE:
# === Usage: ===
# dbo.[AUX:IsPureDate]( <Date> )
*/
function [AUX:IsPureDate]( @DateTime datetime2 )
returns bit


/*==================================================================================================
# OBTAINING LOCALIZED MONTH NAME BY ITS NUMBER:
# === Usage: ===
# dbo.[AUX:MonthName]( <Month NO> )
*/
function [AUX:MonthName]( @Month int )
returns $(dt_MonthName_AUX)
/*==================================================================================================
# OBTAINING LOCALIZED MONTH NAME BY ITS NUMBER (EXT.VERSION):
# === @Options: ===
# $(opt_MonthName_InGenitiveCase_AUX) = 1
# === Usage: ===
# dbo.[AUX:MonthName.Ext]( <Month NO>, null )
# dbo.[AUX:MonthName.Ext]( <Month NO>, 1 )
*/
function [AUX:MonthName.Ext]( @Month int, @Options int=0 )
returns $(dt_MonthName_AUX)

/*==================================================================================================
# OBTAINING LOCALIZED MONTH NAME WITH YEAR, IN FOLLOWING FORM:
# - “<Month name> YYYY”    (EN);
# - “<Month name> YYYY г.” (RU)
# === Usage: ===
# dbo.[AUX:MonthNameWithYear]( <Year>, <Month NO> )
*/
function [AUX:MonthNameWithYear]( @Year int, @Month int )
returns $(dt_MonthNameWithYear_AUX)
/*==================================================================================================
# OBTAINING LOCALIZED MONTH NAME WITH YEAR, IN FOLLOWING FORM:
# - “<Month name> YYYY”    (EN);
# - “<Month name> YYYY г.” (RU)
# (EXTENDED VERSION)
# === @Options: ===
# $(opt_MonthNameWithYear_PostscriptWithYearSign_AUX) = 1
# $(opt_MonthNameWithYear_PostscriptThroughSpace_AUX) = 2
# $(opt_MonthNameWithYear_Default_AUX) = 3
# === Usage: ===
# dbo.[AUX:MonthNameWithYear.Ext]( <Year>, <Month NO>, 0, default )
# dbo.[AUX:MonthNameWithYear.Ext]( <Year>, <Month NO>, 1, default )
# dbo.[AUX:MonthNameWithYear.Ext]( <Year>, <Month NO>, 1, 0 )
# dbo.[AUX:MonthNameWithYear.Ext]( <Year>, <Month NO>, null, null )
*/
function [AUX:MonthNameWithYear.Ext]( @Year int, @Month int,
                                      @Options bit = $(opt_MonthNameWithYear_Default_AUX) )
returns $(dt_MonthNameWithYear_AUX)

/*==================================================================================================
# MONTH LIST WITH NUMBERS AND LOCALIZED NAMES (TABLE FUNCTION):
# === Usage: ===
# select * from [AUX:MonthList]()
*/
function [AUX:MonthList]()
returns @ResultTable table
(
   [NO]              int                  not null,
   [Name]            $(dt_MonthName_AUX)  not null,
   [InGenitiveCase]  $(dt_MonthName_AUX)
)


/*==================================================================================================
# OBTAINING LOCALIZED WEEK DAY NAME BY ITS NUMBER [1..7]:
# === Usage: ===
# dbo.[AUX:WeekDayName]( <Week-day NO> )
*/
function [AUX:WeekDayName]( @WeekDayNo int )
returns $(dt_MonthName_AUX)
/*==================================================================================================
# OBTAINING LOCALIZED WEEK DAY NAME BY ITS NUMBER [1..7] (EXT.VERSION):
# === @Options: ===
# $(opt_MonthName_InGenitiveCase_AUX) = 1
# === Usage: ===
# dbo.[AUX:WeekDayName.Ext]( <Week-day NO>, null )
# dbo.[AUX:WeekDayName.Ext]( <Week-day NO>, 0 )
# dbo.[AUX:WeekDayName.Ext]( <Week-day NO>, 1 ) -- from Sunday
# dbo.[AUX:WeekDayName.Ext]( <Week-day NO>, 2 ) -- from Monday
*/
function [AUX:WeekDayName.Ext]( @WeekDayNo int, @Options int=0 )
returns $(dt_MonthName_AUX)
/*==================================================================================================
# OBTAINING LOCALIZED WEEK DAY NAME BY ITS ID [0..6]:
# === Usage: ===
# dbo.[AUX:WeekDayNameById]( <Week-day ID> )
*/
function [AUX:WeekDayNameById]( @WeekDayId int )
returns $(dt_MonthName_AUX)

/*==================================================================================================
# WEEK DAYS WITH NUMBERS AND LOCALIZED NAMES (TABLE FUNCTION):
# === Usage: ===
# select * from [AUX:WeekDayList]()
*/
function [AUX:WeekDayList]()
returns @ResultTable table
(
   [ID]           int                     not null,
   [NO]           int                     not null,
   [FromSundayNo] int                     not null,
   [FromMondayNo] int                     not null,
   [Name]         $(dt_WeekDayName_AUX)   not null
)


/*==================================================================================================
# CONVERSION FROM DATE TO STRING:
# - “MM/DD/YYYY” (EN);
# - “DD.MM.YYYY” (RU)
# === Usage: ===
# dbo.[AUX:ToString.Date]( <Date> )
*/
function [AUX:ToString.Date]( @Date datetime2 )
returns $(dt_NormalDateStr_AUX)
/*==================================================================================================
# CONVERSION FROM TIME TO STRING:
# - “HH::MM::SS” (EN);
# - “HH::MM::SS” (RU)
# === Usage: ===
# dbo.[AUX:ToString.Time]( <Date-time> )
*/
function [AUX:ToString.Time]( @Time datetime2 )
returns $(dt_NormalTimeStr_AUX)
/*==================================================================================================
# CONVERSION FROM DATE-TIME TO STRING:
# - “MM/DD/YYYY HH::MM::SS” (EN);
# - “DD.MM.YYYY HH::MM::SS” (RU)
# === Usage: ===
# dbo.[AUX:ToString.DateTime]( <Date-time> )
*/
function [AUX:ToString.DateTime]( @DateTime datetime2 )
returns $(dt_WideDateStr_AUX)


/*==================================================================================================
# DATE AS STRING REPRESENTATION:
# - “MM/DD/YYYY”    (EN);
# - “DD.MM.YYYY г.” (RU)
# === Usage: ===
# dbo.[AUX:DateAsString]( <Date> )
*/
function [AUX:DateAsString]( @Date datetime2 )
returns $(dt_WideDateStr_AUX)
/*==================================================================================================
# DATE AS STRING REPRESENTATION:
# - “MM/DD/YYYY”    (EN);
# - “DD.MM.YYYY г.” (RU)
# (EXTENDED VERSION)
# === @Options: ===
# $(opt_MonthNameWithYear_PostscriptWithYearSign_AUX) = 1
# $(opt_MonthNameWithYear_PostscriptThroughSpace_AUX) = 2
# $(opt_DateAsString_Default_AUX) = $(opt_MonthNameWithYear_Default_AUX) = 3
# === Usage: ===
# dbo.[AUX:DateAsString.Ext]( <Date>, null, null )
# dbo.[AUX:DateAsString.Ext]( <Date>, 1, null )
# dbo.[AUX:DateAsString.Ext]( <Date>, 1, 0 )
*/
function [AUX:DateAsString.Ext]( @Date datetime2,
                                 @Options bit = $(opt_DateAsString_Default_AUX) )
returns $(dt_WideDateStr_AUX)

/*==================================================================================================
# DATE-TIME AS STRING REPRESENTATION:
# - “MM/DD/YYYY HH:MM:SS” (EN);
# - “DD.MM.YYYY HH:MM:SS” (RU)
# === Usage: ===
# dbo.[AUX:DateTimeAsString]( <Date-time> )
*/
function [AUX:DateTimeAsString]( @DateTime datetime2 )
returns $(dt_WideDateStr_AUX)
/*==================================================================================================
# DATE-TIME AS STRING REPRESENTATION:
# - “MM/DD/YYYY HH:MM:SS” (EN);
# - “DD.MM.YYYY HH:MM:SS” (RU)
# (EXTENDED VERSION)
# === @Options: ===
# $(opt_MonthNameWithYear_PostscriptWithYearSign_AUX) = 1
# $(opt_MonthNameWithYear_PostscriptThroughSpace_AUX) = 2
# $(opt_DateTimeAsString_Default_AUX) = $(opt_MonthNameWithYear_Default_AUX) = 3
# === Usage: ===
# dbo.[AUX:DateTimeAsString.Ext]( <Date-time>, null, null )
# dbo.[AUX:DateTimeAsString.Ext]( <Date-time>, 1, 0 )
# dbo.[AUX:DateTimeAsString.Ext]( <Date-time>, 0, 0 )
*/
function [AUX:DateTimeAsString.Ext]( @DateTime datetime2,
                                     @Options bit = $(opt_DateTimeAsString_Default_AUX) )
returns $(dt_WideDateStr_AUX)


/*==================================================================================================
# MAKING DATE FROM YEAR, MONTH AND DAY:
# === Usage: ===
# dbo.[AUX:DateValue.YMD]( <Year>, <Month>, <Day> )
*/
function [AUX:DateValue.YMD]( @Year int, @Month int, @Day int )
returns datetime2


/*==================================================================================================
# DETERMINING NUMBER OF DAYS IN MONTH:
# === Usage: ===
# dbo.[AUX:DaysInMonth]( <Year>, <Month> )
*/
function [AUX:DaysInMonth]( @Year int, @Month int )
returns int
/*==================================================================================================
# DETERMINING NUMBER OF DAYS IN MONTH (BY DATE VALUE):
# === Usage: ===
# dbo.[AUX:DaysInMonth.ByDate]( <Date> )
*/
function [AUX:DaysInMonth.ByDate]( @Date datetime2 )
returns int
