How to add number of days to date column in query

I have invoices table where I have date and pay_deadline. Pay deadline represent number of days from date of invoice that you have a time for pay. For example date=09/01/2024 and pay_deadline=7 that means that you have 7 days from 09/01/2024 to pay.

My question is:

How can I query for example all invoices that are still in time slot to pay? That means now() is between date and date + pay_deadline

Invoice::query()
    ->whereRaw(...)

LaryAI

1 year ago

Level 58

To solve this problem, you can use the whereRaw method to compare the current date with the date and date + pay_deadline in your query. Here's how you can do it:

use Carbon\Carbon;

$now = Carbon::now();

Invoice::query()
    ->whereRaw('? BETWEEN `date` AND DATE_ADD(`date`, INTERVAL `pay_deadline` DAY)', [$now])
    ->get();

Explanation:

Carbon::now() gets the current date and time.
whereRaw allows you to write a raw SQL query.
? BETWEEN date AND DATE_ADD(date, INTERVAL pay_deadline DAY) checks if the current date ($now) is between the date of the invoice and the date plus the pay_deadline days.

This query will return all invoices that are still within the time slot to pay.

knubbe

1 year ago

Level 36

@LaryAI Great! How to query same table but where date + pay_deadline is less then NOW()?

Snapey

1 year ago

Level 122

@knubbe what is date in this context?

tykus

1 year ago

Level 104

@knubbe

How to query same table but where date + pay_deadline is less then NOW()

Am I reading you right; date + pay_deadline < NOW() will be almost every record in the table; is this what you want?

knubbe

1 year ago

Level 36

@tykus exactly

knubbe

1 year ago

Level 36

@Snapey date of invoice

tykus

1 year ago

Best Answer

Level 104

@knubbe in that case, the following query should work:

Invoice::query()
    ->whereRaw("DATE_ADD(`date`, INTERVAL `pay_deadline` DAY) < ?", [$now])
    ->get();

1 like

Please or to participate in this conversation.