CODEWITHSUNDEEP
mysqlsqldategenerate
user11193267
user11193267

Reputation:

Generating a date in a table SQL

I have a database in SQL which contains information about students and books they are borrowing.

I have a table of loans which includes the book_id, the student_id, the day the book was taken and the number of days the book can be kept.

I want to auto-generate the return_date by adding the days at the date in which the book was taken. How can I do this using MySQL Workbench?

Upvotes: 1

Views: 186

Answers (3)

VBoka
VBoka

Reputation: 9083

You can use a combination of interval and + :

select book_id
       , student_id
       , date_taken
       , number_od_days
       , (date_taken + interval number_od_days day) as return_date
from loans;

Here is the example in the DEMO

If you want this column to be auto incremented I suggest you create a trigger in your table:

CREATE TRIGGER rentcheck 
BEFORE INSERT ON loans 
FOR EACH ROW 
begin
    if new.number_od_days < 0 or new.number_od_days is null then
      signal sqlstate '45000';
    else
      set new.return_date = (new.date_taken + interval new.number_od_days day);
    end if;
end;

With it you can control if the column number_of_days will be negative or not entered because that would result with non logical data. Here is the demo for the trigger: DEMO

Upvotes: 1

GMB
GMB

Reputation: 222502

You can use MySQL datetime function date_add() to compute the target return_date. Assuming that the date when the book was loaned is called loan_date and that the duration is stored in loan_duration, expressed in days:

select
    book_id,
    student_id,
    loan_date,
    date_add(loan_date, interval loan_duration day) return_date
from loan_table

In an update statement:

update loan_table
set return_date = date_add(loan_date, interval loan_duration day)
where return_date is null -- only compute the return_dates that were not yet computed

Edit

If you want that value to be auto-generated, an option is to use a generated stored (or virtual) column (available since MySQL 5.7). This works by defining a computation rule, that MySQL will automatically apply when a record is inserted or updated.

Demo on DB Fiddle:

-- set up
create table loan_table(
    id int primary key auto_increment,
    book_id int,
    student_id int,
    loan_date date,
    loan_duration int,
    -- here is the generated column
    return_date date 
        as (date_add(loan_date, interval loan_duration day)) stored
);

-- perform an insert
insert into loan_table(book_id, student_id, loan_date, loan_duration)
values(1, 1, '2019-11-02', 3)

-- check the computed value
select * from loan_table;

id | book_id | student_id | loan_date  | loan_duration | return_date
-: | ------: | ---------: | :--------- | ------------: | :----------
 1 |       1 |          1 | 2019-11-02 |             3 | 2019-11-05

Upvotes: 1

Rampage64
Rampage64

Reputation: 158

you can use variables in mysql: 

set @days = 10;
set @mydate := DATE_ADD("2019-11-01", INTERVAL @days DAY);
select @mydate;

and then use your insert statement:

Insert into Loan(... ,[return_date])
values (...., @mydate)

Hope it helps

Upvotes: 0

Related Questions