Oracle посчитать количество месяцев
мне нужно, например, вывод для этого запроса должен быть либо 2 месяцев и 14 дней в худшем случае, иначе я не буду возражать, если я могу иметь точные дни после месяца, потому что эти дни на самом деле не 14, потому что все месяцы не в течение 30 дней.
обновлено для корректности. Первоначально ответил @jen.
SQLFiddle демо :
обратите внимание, как за последние две даты Oracle сообщает десятичную часть месяцев (которая дает дни) неправильно. 0.1290 соответствует 4 дней с Oracle учитывая 31 дней в месяц (для Марта и апреля).
Я думаю, что ваш вопрос недостаточно хорошо определен по следующей причине.
ответы, основанные на months_between, должны иметь дело со следующей проблемой: что функция сообщает ровно один месяц между 2013-02-28 и 2013-03-31, и между 2013-01-28 и 2013-02-28, и между 2013-01-31 и 2013-02-28 (я подозреваю, что некоторые ответчики не использовали эти функции на практике или теперь придется пересмотреть какой-то производственный код!)
Это документированное поведение, в котором даты, которые являются последними в соответствующих месяцах или которые приходятся на один и тот же день месяца, считаются целым числом месяцев.
таким образом, вы получаете тот же результат "1" при сравнении 2013-02-28 с 2013-01-28 или с 2013-01-31, но сравнение его с 2013-01-29 или 2013-01-30 дает 0.967741935484 и 0.935483870968 соответственно - так как одна дата приближается к другой, разница, сообщаемая этой функцией, может увеличение.
Если это неприемлемая ситуация, вам придется написать более сложную функцию или просто положиться на расчет, который предполагает 30 (например) дней в месяц. В последнем случае, как вы будете иметь дело с 2013-02-28 и 2013-03-31?
это то, что вы сделали ?
здесь я просто делаю разницу между сегодняшним днем и CREATED_DATE DATE поле в таблице, которое, очевидно, является датой в прошлом:
Я использую (365/12), или 30.416667, как мой коэффициент преобразования, потому что я использую общее количество дней и удаление лет и месяцев (Как дней), чтобы получить оставшееся количество дней. Во всяком случае, для моих целей этого было достаточно.
MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
SYSDATE
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
SELECT SYSDATE d,
D
D1
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), 'DD') d1
D
D1
12.7 Date and Time Functions
This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.
Table 12.11 Date and Time Functions
Name | Description |
---|---|
ADDDATE() | Add time values (intervals) to a date value |
ADDTIME() | Add time |
CONVERT_TZ() | Convert from one time zone to another |
CURDATE() | Return the current date |
CURRENT_DATE() , CURRENT_DATE | Synonyms for CURDATE() |
CURRENT_TIME() , CURRENT_TIME | Synonyms for CURTIME() |
CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP | Synonyms for NOW() |
CURTIME() | Return the current time |
DATE() | Extract the date part of a date or datetime expression |
DATE_ADD() | Add time values (intervals) to a date value |
DATE_FORMAT() | Format date as specified |
DATE_SUB() | Subtract a time value (interval) from a date |
DATEDIFF() | Subtract two dates |
DAY() | Synonym for DAYOFMONTH() |
DAYNAME() | Return the name of the weekday |
DAYOFMONTH() | Return the day of the month (0-31) |
DAYOFWEEK() | Return the weekday index of the argument |
DAYOFYEAR() | Return the day of the year (1-366) |
EXTRACT() | Extract part of a date |
FROM_DAYS() | Convert a day number to a date |
FROM_UNIXTIME() | Format Unix timestamp as a date |
GET_FORMAT() | Return a date format string |
HOUR() | Extract the hour |
LAST_DAY | Return the last day of the month for the argument |
LOCALTIME() , LOCALTIME | Synonym for NOW() |
LOCALTIMESTAMP , LOCALTIMESTAMP() | Synonym for NOW() |
MAKEDATE() | Create a date from the year and day of year |
MAKETIME() | Create time from hour, minute, second |
MICROSECOND() | Return the microseconds from argument |
MINUTE() | Return the minute from the argument |
MONTH() | Return the month from the date passed |
MONTHNAME() | Return the name of the month |
NOW() | Return the current date and time |
PERIOD_ADD() | Add a period to a year-month |
PERIOD_DIFF() | Return the number of months between periods |
QUARTER() | Return the quarter from a date argument |
SEC_TO_TIME() | Converts seconds to 'hh:mm:ss' format |
SECOND() | Return the second (0-59) |
STR_TO_DATE() | Convert a string to a date |
SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments |
SUBTIME() | Subtract times |
SYSDATE() | Return the time at which the function executes |
TIME() | Extract the time portion of the expression passed |
TIME_FORMAT() | Format as time |
TIME_TO_SEC() | Return the argument converted to seconds |
TIMEDIFF() | Subtract time |
TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
TIMESTAMPADD() | Add an interval to a datetime expression |
TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
TO_DAYS() | Return the date argument converted to days |
TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
UNIX_TIMESTAMP() | Return a Unix timestamp |
UTC_DATE() | Return the current UTC date |
UTC_TIME() | Return the current UTC time |
UTC_TIMESTAMP() | Return the current UTC date and time |
WEEK() | Return the week number |
WEEKDAY() | Return the weekday index |
WEEKOFYEAR() | Return the calendar week of the date (1-53) |
YEAR() | Return the year |
YEARWEEK() | Return the year and week |
Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:
The query also selects rows with dates that lie in the future.
Functions that expect date values usually accept datetime values and ignore the time part. Functions that expect time values usually accept datetime values and ignore the date part.
Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to CURDATE() , CURTIME() , UTC_DATE() , UTC_TIME() , UTC_TIMESTAMP() , and to any of their synonyms.
The CURRENT_TIMESTAMP() , CURRENT_TIME() , CURRENT_DATE() , and FROM_UNIXTIME() functions return values in the current session time zone, which is available as the session value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the session time zone. See Section 5.1.13, “MySQL Server Time Zone Support”.
Some date functions can be used with “ zero ” dates or incomplete dates such as '2001-11-00' , whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a nonzero value. For example:
Other functions expect complete dates and return NULL for incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names. For example:
Several functions are strict when passed a DATE() function value as their argument and reject incomplete dates with a day part of zero: CONVERT_TZ() , DATE_ADD() , DATE_SUB() , DAYOFYEAR() , TIMESTAMPDIFF() , TO_DAYS() , TO_SECONDS() , WEEK() , WEEKDAY() , WEEKOFYEAR() , YEARWEEK() .
Fractional seconds for TIME , DATETIME , and TIMESTAMP values are supported, with up to microsecond precision. Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate.
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD() . The related function SUBDATE() is a synonym for DATE_SUB() . For information on the INTERVAL unit argument, see Temporal Intervals.
When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr .
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in Section 5.1.13, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. The TIMESTAMP range is described in Section 11.2.1, “Date and Time Data Type Syntax”.
To use named time zones such as 'MET' or 'Europe/Amsterdam' , the time zone tables must be properly set up. For instructions, see Section 5.1.13, “MySQL Server Time Zone Support”.
Returns the current date as a value in ' YYYY-MM-DD ' or YYYYMMDD format, depending on whether the function is used in string or numeric context.
В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная — время. Это позволяет совершать над датами арифметические операции — сложение и вычитание.
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Маска
Назначение
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'CC') d2
D1
D2
Столетие, причем перед датами до нашей эры ставится знак «минус».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE-1000000, 'SCC') d2
D1
D2
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'YYYY') d2
D1
D2
Аналогично YYYY, но перед датами до нашей эры ставится знак «минус»
Аналогичны YYYY, но возвращаются соответственно последние 3,2 или 1 цифра года.
мне нужно вычислить количество полное месяц в SQL, т. е.
- 2009-04-16 до 2009-05-15 => 0 полный месяц
- 2009-04-16 до 2009-05-16 => 1 полный месяц
- 2009-04-16 до 2009-06-16 = > 2 полных месяца
Я попытался использовать DATEDIFF, т. е.
но вместо того, чтобы давать мне полные месяцы между двумя датами, это дает мне разницу в части месяца, т. е.
кто-нибудь знаете, как рассчитать количество полных месяцев в SQL Server?
Это ORACLE только и не для SQL-Server:
и за полный месяц:
может использоваться в Oracle 8i и выше.
функция dateadd может использоваться для смещения к началу месяца. Если у endDate есть часть дня меньше, чем startDate, она будет перенесена на предыдущий месяц, таким образом datediff даст правильное количество месяцев.
каково ваше определение месяца? Технически в месяц может быть 28,29,30 или 31 день в зависимости от месяца и високосного года.
похоже, вы рассматриваете месяц как 30 дней, так как в вашем примере вы проигнорировали, что май имеет 31 день, так почему бы просто не сделать следующее?
это отлично работает для меня на SQL SERVER 2000.
Не обязательно создавать функцию только в части @result. Например:
этот ответ следует за форматом T-SQL. Я осмысляю эту проблему как одну из линейного времени расстояние между двумя точками в datetime формат, называть их Время1 и Время2; Время1 должны быть согласованы с 'в момент' стоимостью Вы имеете дело с (скажем, дату рождения или виджет дата создания или дата начала) и time2 должны быть приведены в соответствие с новым временем' значение (скажем, даты снимка, или виджет даты завершения или контрольно-пропускного пункта-достигнута дата).
решение использует простое измерение, преобразование и вычисления последовательных пересечений нескольких циклов разной длины; здесь: век,десятилетие,год,месяц,день (Спасибо календарю майя за концепцию!). Я благодарю других участников Stack Overflow за то, что они показали мне некоторые функции компонентов в этом процессе, которые я сшил вместе. Я положительно оценил их в свое время на этом форуме.
во-первых, построить горизонт-это линейный набор пересечений столетий, десятилетий,лет, месячных циклов, инкрементных по месяцам. Для этого используйте декартову функцию cross join. (Подумайте об этом как о создании ткани, из которой мы будем вырезать длину между двумя точками "гггг-мм", чтобы измерить расстояние):
затем преобразуйте точки даты Time1 и Time2 в формат "гггг-мм" (подумайте об этом как о координатных точках разреза на всей ткани). Сохранить первоначальную datetime версии точек, а также:
затем выберите порядковое расстояние единиц "гггг-мм", меньше одного, чтобы преобразовать в кардинальное расстояние (т. е. вырезать кусок ткани из всей ткани в определенных точках разреза и получить его сырое измерение):
RAW Выход: Я называю это "сырым расстоянием", потому что компонент месяца кардинального расстояния "гггг-мм" может быть слишком большим; компоненты дневного цикла в течение месяца нужно сравнить, чтобы увидеть, должно ли это значение последнего месяца считаться. В этом примере, в частности, расстояние вывода raw равно '12'. Но это неправильно, так как 12/14 до 12/15, поэтому только 11 полных месяцев истекли-его только один день не проходит через 12-й месяц. Поэтому мы должны ввести внутримесячный дневной цикл, чтобы получить окончательный ответ. Вставьте сравнение позиций "месяц, день" между ними, чтобы определить, считается ли номинально последний месяц точки даты, или не:
Окончательный Результат: Правильный ответ '11' теперь наш выход. Надеюсь, это поможет. Спасибо!
Oracle реализует набор функций для работы со значениями типа даты/времени. Мы не будем подробно рассматривать все функции, но сводка в табл. 1 познакомит Вас с доступными возможностями. Если какие-то функции вас заинтересуют, обращайтесь за подробным описанием к справочнику Oracle SQL Reference.
Избегайте использования традиционных функций Oracle, обрабатывающих значения типа DATE , при работе с новыми типами данных TIMESTAMP . Вместо них следует по возможности использовать новые функции для типов INTERVAL . А DATE -функции должны использоваться только для обработки значений типа DATE .
Многие из приведенных в табл. 1 функций (в том числе ADD_MONTHS ) получают значения типа DATE . При использовании таких функций с новыми типами данных TIMESTAMP могут возникнуть проблемы. Хотя любой из этих функций можно передать значение типа TIMESTAMP , Oracle неявно преобразует его к типу DATE , и только тогда функция выполнит свою задачу, например:
В этом примере переменная ts содержит значение типа TIMESTAMP WITH TIME ZONE . Это значение неявно преобразуется в DATE при передаче LAST_DAY . Поскольку в типе DATE не сохраняются ни дробные части секунд, ни смещение часового пояса, эти части значения ts попросту отбрасываются. Результат LAST_DAY снова присваивается ts , что приводит к выполнению второго неявного преобразования — на этот раз DATE преобразуется в TIMESTAMP WITH TIME ZONE . Второе преобразование получает часовой пояс сеанса, поэтому в смещении часового пояса в итоговом значении мы видим ?05:00.
Очень важно понимать эти преобразования. и избегать их. Несомненно, вы представляете, какие коварные ошибки могут появиться в программе из-за неосторожного использования функций DATE со значениями TIMESTAMP . Честно говоря, я не представляю, почему в Oracle встроенные функции DATE не были перегружены для нормальной работы с TIMESTAMP . Будьте осторожны!
Функция TO_DATE(str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» — она возникает при указании недопустимой форматной маски.
SELECT TO_DATE('12.09.2006') d
D
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения.
Маска
Назначение
Первый день столетия
YEAR, или YYYY, или YY, или Y
Первый день года
Первый день квартала
MONTH, или MON, или MM
Первый день месяца
Тот же день недели, что и первый день текущего года
Тот же день недели, что и первый день текущего месяца
DAY, или DY, или D
Первый день недели
HH, или HH12, или HH24
Функция MONTHS_BETWEEN(d1, d2)
Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN('2.09.2006', '2.05.2006') d1,
MONTHS_BETWEEN('12.09.2006', '2.05.2006') d2,
MONTHS_BETWEEN('2.05.2006', '12.09.2006') d3
D1
D2
D3
Функция TRUNC(d[,mask])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
SELECT SYSDATE d1,
D1
D2
Рассмотрим типовые примеры — усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, 'HH24') d2,
TRUNC(SYSDATE, 'DD') d3,
TRUNC(SYSDATE, 'MM') d4,
TRUNC(SYSDATE, 'YYYY') d5
D1
D2
D3
D4
D5
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'DD.MM.YY HH24:MI') d2
D1
D2
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD».
SELECT SYSDATE d1,
ROUND(SYSDATE, 'HH24') d3,
ROUND(SYSDATE, 'DD') d4,
ROUND(SYSDATE, 'MM') d5,
ROUND(SYSDATE, 'YYYY') d6
D1
D2
D3
D4
D5
D6
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1,
ADD_MONTHS(SYSDATE, -3) d2
D
D1
D2
Читайте также: