Тег: функции времени

Функции для работы с датами и временем sql

Как получить начало месяца и квартала в 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 можно не только получать начало периода, но и отсекать дробную часть числа.

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

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

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

Полный возраст в 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.

Високосный год в SQL.

По григорианскому календарю год считается високосным если он кратен 4, но при этом не кратен 100, либо кратен 400. С учетом этого можем написать функцию для определения “високосности” года.

create function [dbo].[isLeapYear]
(@date date)
returns bit
as
begin
return (case
			when year(@date) % 4 = 0 and year(@date) % 100 != 0
			then 1
			when year(@date) % 400 = 0
			then 1
			else 0
		end)
end

Функция может принимать любую дату в качестве аргумента и возвращает единицу если год високосный, в противном случае – 0:

Пример использования функции isLeapYear

Количество дней в месяце SQL.

Узнать количество дней в месяце в t-SQL можно таким образом: 

declare @date datetime = getdate()
declare @dayCount int
set @dayCount = day(dateadd(dd, -day(dateadd(mm, 1, @date)), dateadd(mm, 1, @date)))
select @dayCount

В этом коде количество дней в месяце определяется функцией day от последнего числа месяца. Последнее число месяца в моем случае находится добавлением к текущей дате одного месяца и “вычитанием” от неё дня месяца полученной даты. Начиная с MS SQL 2012ой версии, последнее число можно найти с использованием встроенной функции EOMonth. Т.е. необходимо третью строку в коде выше заменить следующей строкой:

set @dayCount = day(eomonth(@date))

В итоге наша функция будет выглядеть так:

create function [dbo].[monthDayCount]
(@date datetime)
returns int
as
begin
return (day(dateadd(dd, -day(dateadd(mm, 1, @date)), dateadd(mm, 1, @date))))
end