Retrieve the data for all previous months of the same year except today's date

0
votes

Need help in writing the query to get the data for all previous months of the same year.

If today's date is July 20 2017, I need to retrieve the data for all previous months of the same year except today's date(July 20 2017).

If date is July 20 2017, data should consists of January'17 till 19 July 2017 and so on.

If date is July 21 2017, data should consists of January'17 till 20 July 2017

Thanks

4
sqlsql-server
We would love to help but first you need to show us what you've already tried. SO is not a code writing service. - Zohar Peled
I have tried this - Swati Jain
To get the current day at 00:00:00 hours you can just cast to DATE datatype (removing the time) and cast back to DATETIME if necessary CAST(CAST(GETDATE() AS DATE) AS DATETIME). Other than that you need to provide an minimal reproducible example for us to help you. - Adwaenyth
select DATEPART(m, DATEADD(m, 0, getdate())), DATEPART(yyyy, DATEADD(m, -1, getdate())) .. But this reteriving data only the current month and also it is not excluding the current date - Swati Jain

4 Answers

1
votes

Without seeing code its difficult but something like this in the where clause ....

WITH DATES AS (
SELECT GETDATE() AS MYDATE
UNION
SELECT CONVERT(DATETIME,'01/01/1999 12:00:00.000')
UNION
SELECT CONVERT(DATETIME,'01/02/2016 12:00:00.000')
UNION
SELECT CONVERT(DATETIME,'01/04/2007 12:00:00.000')
UNION
SELECT CONVERT(DATETIME,'01/05/2005 12:00:00.000')
UNION
SELECT CONVERT(DATETIME,'01/05/2017 12:00:00.000')
UNION
SELECT CONVERT(DATETIME,'01/01/2017 14:00:00.000')
)
SELECT * FROM DATES
WHERE 
CONVERT(DATE,DATES.MYDATE) < CONVERT(DATE,GETDATE())
AND 
DATEPART(YEAR,DATES.MYDATE) = DATEPART(YEAR,GETDATE())
1
votes
SELECT *
FROM yourTable
WHERE
    dateCol >= DATEADD(yy,  DATEDIFF(yy,  0, GETDATE()), 0) AND
    dateCol < CAST(GETDATE() AS DATE)

Demo

1
votes

This is just a question how to get January 1st of current year, and how to get yesterday. There are various ways of doing it. One of the ways is below and other answers with different ideas are also fine.

SELECT * FROM YourTable
WHERE YourDateCol >= DATEFROMPARTS(YEAR(GETDATE()),1,1)
AND YourDateCol < CAST(GETDATE() AS DATE)

For yesterday, you actually take today at 0:00 and get everything before that.

0
votes

To do this in an efficient way, place two variables which will contain first day of the year and today's date.

DECLARE @firstDayOfYear as DateTime;
DECLARE @today as DateTime;

SET @firstDayOfYear = DATEFROMPARTS(YEAR(GETDATE()),1,1);
SET @today = CAST(GETDATE() AS DATE);

SELECT * FROM [table]
WHERE [dateColumnToCompare] >= @firstDayOfYear
AND [dateColumnToCompare] < @today;