SQL Server: Get Data for Only the Past Year

SQL Server: Get data for only the past year

The following adds -1 years to the current date:

SELECT ... From ... WHERE date > DATEADD(year,-1,GETDATE())

SELECT previous year up to current date

I think that this is your requirement:

WHERE start_date BETWEEN
  DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 1) 
  AND
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

With:

DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 1)

you get the 1st day of current month in last year.

With:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

you get the last Day of previous month.

See the demo.

How to get data from previous years till previous month using SQL Query?

I am trying to fetch 2years data till 28-feb-2021 using SQL.

The logic for this is:

date >= '2019-03-01' and
date < '2021-03-01'

To base this on the current date:

date >= dateadd(year, -2, datefromparts(year(getdate()), month(getdate()), 1)) and
date < datefromparts(year(getdate()), month(getdate()), 1)

Extracting data from only the year

You should avoid storing your dates as text, but rather should use a proper date column. That being said, you may check the right 4 characters of the date string:

SELECT *
FROM yourTable
WHERE RIGHT(date_col, 4) = '2021';

If the column be an actual date type then use:

SELECT *
FROM yourTable
WHERE date_col >= '2021-01-01' AND date_col < '2022-01-01';

Query to return data only within the last year of a columns date

Try with add_months :

WHERE EXPIRATION_DATE >= add_months( trunc(sysdate), -12);

How do I find data from this day exactly one year ago?

It should be:

epe_curremploymentdate = DATEADD(year, -1, GETDATE())

Don't check for year in your where clause.

EDIT:

Simple: use

cast(epe_curremploymentdate AS DATE) = cast(DATEADD(year, -1,
  GETDATE()) AS DATE)

That is, if you are using SQL Server 2008 and above.

Get data for last one year from database with whole month data

To get the first of the month of the previous year, use:

DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'))

Your query should be:

SELECT * 
FROM TABLE 
WHERE 
    date > DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'))
ORDER BY date

Reference:

• Some Common Date Routines

---

Related Topics