Как получить начало месяца и квартала в Oracle?

Мы уже рассматривали как получить начало месяца и квартала в t-SQL. Сейчас покажу как это сделать в Oracle.
В Oracle сделать это намного проще c помощью встроенной функции trunc:

select  trunc(sysdate, 'MM') as monthStart
        , trunc(sysdate, 'Q') as quarterStart
from    dual

Функция в Oracle для начала месяца может выглядеть так:

create or replace function monthStart(inputDate in date)
  return date
as 
begin
  return trunc(inputDate, 'MM');
end;

Применение функции:
select monthStart(sysdate) as ms from dual

Функция для начала квартала:

create or replace function quarterStart(inputDate in date)
  return date
as 
begin
  return trunc(inputDate, 'Q');
end;

Применение функции:
select quarterStart(sysdate) as ms from dual

С помощью функции trunc можно не только получать начало периода, но и отсекать дробную часть числа.

Количество вхождений подстроки в строку в SQL

В некоторых задачах SQL бывает необходимо подсчитать количество вхождений подстроки в строку. Самое простое решение в таком случае сводится к следующему алгоритму (строка, в которой ведется поиск – A, подстрока – B):

  1. Определить длину строки A.
  2. Заменить в строке A все подстроки B на пустые подстроки.
  3. Определить длину строки, полученной во втором шаге.
  4. Из длины, полученной на первом шаге вычесть длину, полученный в третьем шаге.
  5. Результат, полученный в четвертом шаге, поделить на длину подстроки B.

Оформим приведенный алгоритм в t-SQL в виде функции с двумя параметрами, где переменная @where – это строка A, а @what – подстрока B:

create function [dbo].[substring_cnt]
    (@where varchar(max), @what varchar(100))
returns int
as
begin
    return ((datalength(@where) - datalength(replace(@where, @what, ''))) / datalength(@what))
end

.

В своей следующей записи я покажу как реализовать приведенную в этой статье функцию в виде CLR-функции.

Первое число месяца в Excel.

Я уже тут писал о том, как получить первое число месяца в Transact-SQL. Сейчас напишу как это сделать в Excel’e.
Существуют много способов сделать это. Мне нравятся два из них:
1) От исходной даты вычитаем номер дня в месяце – получаем последний день месяца предшествующий месяцу исходной даты. К этому результату прибавляем один день. Эта дата и будет нашим результатом. Формула для этого способа: “=A1-ДЕНЬ(A1)+1″.
=A1-ДЕНЬ(A1)+1

2) С помощью функции ДАТА(год;месяц;день) и из составляющих исходной даты (год и месяц) получаем первое число: “=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)”.
=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

Последнее вхождение подстроки в строку – last charindex

Всем известно про функцию t-sql charindex, которая возвращает начальную позицию подстроки в строке. Но некоторых случаях необходимо бывает найти начальную позицию не первого вхождения подстроки, а последнего. Для этого я написал пользовательскую функцию last_charindex. Ниже приведу ее код:

create function [dbo].[last_charindex]
	(@what varchar(1000), @where varchar(1000))
returns int
as
begin
	set @where = reverse(@where)
	set @what = reverse(@what)

	return (case
			when charindex(@what, @where) > 0
			then len(@where) - (len(@what) + charindex(@what, @where)) + 2
			else 0
	end)
end

Полный возраст в MS SQL.

В MS SQL Server есть очень удобная и замечательная встроенная функция datediff, которая возвращает разницу между двумя датами. Например, следующий запрос вернет разницу в годах между 31-м декабря 2014 года и 1-м января 2015 года:

select datediff(year, '2014-12-31', '2015-01-01')

Результат запроса будет равен 1, хотя разница между двумя всего 1 день. Дело в том, что datediff возвращает количество пересеченных границ указанных аргументом datepart, за период времени между аргументами startdate и enddate функции.
Такой результат не очень устраивает нас, если мы хотим определить количество полных лет между двумя датами, т.е. возраст на определенную дату. Немного дополним вывод этой функции и оформим в виде функции:

create function [dbo].fullAge
(@startdate datetime
, @enddate datetime)
returns int
as
begin
return	(datediff(year, @startdate, @enddate) -
		case
			when month(@startdate) < month(@enddate)
			then 0
			when month(@startdate) > month(@enddate)
			then 1
			when day(@startdate) > day(@enddate)
			then 1
			else 0
		end)
end

Здесь вначале находится разница в годах между двумя календарными датами функцией datediff и от неё вычитается 1, если в календарном году даты @enddate месяц и дата переменной @startdate еще не наступили относительно переменной @enddate.