1

Topic: Operation with date, MySQL

Kind time of days, faced with interesting (skilled hardly it seems interesting) a problem. I search the help in its decision for at the experience yet does not suffice.
Initial conditions:
There is a table subscriptions, with fields of the identifier entering into it sb_book and period of output of books sb_start (data type at sb_start - Date).
The task sounds so:
To show identifiers and dates of output of books for the first year of operation of library (to consider all dates from the first output of the book till 31th December (inclusively) that year when the library started to work first year of operation of library).
Tried to make it thus, however syntax SQL does not allow to make it:

select sb_book, sb_start from subscriptions
where sb_start> = (select min (sb_start) from subscriptions) and
sb_start <= (select max (sb_start) from subscriptions where year (sb_start) = year (select min (sb_start) from subscriptions));

That is I find minimum date among all dates in the field sb_ start - the beginning of year and I try to find the maximum date among those which year coincides with a year minimum, but sql does not allow to fulfill the last subquery.

2

Re: Operation with date, MySQL

If it is not enough data, descends so:

SELECT *
FROM subscriptions
WHERE YEAR (sb_start) = (SELECT YEAR (MIN (sb_start)) FROM subscriptions)

If it is a lot of data, better so:

SELECT *
FROM subscriptions
WHERE sb_start <(SELECT MAKEDATE (YEAR (MIN (sb_start)) +1,1) FROM subscriptions)

the index across the field sb_start or beginning with it Also is required.

3

Re: Operation with date, MySQL

miksoft, Many thanks! Tell, whether the bad practice is the decision of the given sort of tasks two requests? I.e. if I found minimum date in one request, looked for a year and would fulfill other request taking into account the found year in last request?

4

Re: Operation with date, MySQL

ALEX0802 wrote:

Tell, whether the bad practice is the decision of the given sort of tasks two requests? I.e. if I found minimum date in one request, looked for a year and would fulfill other request taking into account the found year in last request?

it is Needlessly better so not to do. But as an educational variant descends.

5

Re: Operation with date, MySQL

miksoft, Thanks!

6

Re: Operation with date, MySQL

miksoft wrote:

it is Needlessly better so not to do.

Well, it is necessary to transaction to study, their insulation. Heavy...