Sql Select All Data That Past 45 Days

SQL select all data that past 45 days

In MSSQL DATEDIFF(interval, date1, date2) returns interval of date2 - date1.

Interval should be selected from this list:

- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear = Day of the year
- day, dy, y = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond`

Then use:

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon'
AND DATEDIFF(day, targetDate, GETDATE() ) > 45

For MySQL you can use TIMESTAMPDIFF(unit,date1,date2) which returns interval of date1 - date2.

unit can be selected from MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon'
AND TIMESTAMPDIFF(DAY, CURDATE( ), targetDate) > 45

how to get data whose expired within 45 days..?

If you are using mysql then try DATEDIFF.

for 45 days

select * from `table` where DATEDIFF(now(),expireydate)<=45;

for 30 days

select * from `table` where DATEDIFF(now(),expireydate)<=30;

---

In oracle - will do the trick instead of datediff and SYSDATE instead of now().[not sure]

---

In sql server DateDiff is quite different you have to provide unit in which difference to be taken out from 2 dates.

DATEDIFF(datepart,startdate,enddate)

to get current date try one of this: CURRENT_TIMESTAMP or GETDATE() or {fn NOW()}

SQL statement to select all rows from previous day

get today no time:

SELECT dateadd(day,datediff(day,0,GETDATE()),0)

get yestersday no time:

SELECT dateadd(day,datediff(day,1,GETDATE()),0)

query for all of rows from only yesterday:

select 
    * 
    from yourTable
    WHERE YourDate >= dateadd(day,datediff(day,1,GETDATE()),0)
        AND YourDate < dateadd(day,datediff(day,0,GETDATE()),0)

SQL Select Records based on current date minus two days

you should try to use dateadd function

select * from orders where orderdate > dateadd(dd,-1,cast(getdate() as date))

Now, this may be exactly what you need but then you need to understand that by casting to date we remove the time part and effectively go back to the start of the day and a day behind it(-1) gives the start of yesterday.

Mysql query select all dates (day & month only) within next 45 days

Worked this out with php

// Get the dates m-d for the next 45 days
for ($i = 0; $i <= 45; $i++) {
    $days = '+' . $i . ' days';
    $date = date('m-d', strtotime($days));
    if ($i != 45) {
        $DateQuery .= "'$date',";
    } else {
        $DateQuery .= "'$date'";
    }
}

and Inserted the dates I needed directly into the query

    SELECT
       p.*,
       c.company 
    FROM
       products p 
       LEFT JOIN
          customers c 
          ON c.id = p.id 
    WHERE
       DATE_FORMAT(p.service_date, '%m-%d') IN 
       (
           $DateQuery
       )
       AND c.email_service = 1 
    ORDER BY
       p.service_date ASC

SQL to get all data for the last 30 days from a PostgreSQL database

Just use current_timestamp (documented here):

select *
from school
where created_time > current_timestamp - interval '30 day';

SELECT all records that are 30 days old

This is what I used. Very simple

$sth = $dbh->prepare(qq(SELECT * FROM people WHERE updatestatus + INTERVAL 30 DAY <=     NOW() )) or die $DBI::errstr;

---

Related Topics