SQL: Calculate data based on the date for 270 days.

Question

Hi team - I have a requirement where i need to pull the records from the tableA based on the PostingDate. The posting date is in the int format.

For Example for today's date: 20,220,302 this is how the format is. Additional examples are for Feb 28 2022 it is in the format 20,220,228.

Now the requirement is i need to pull the data from Jan 1, 2021, to 270 days. If i run the query today, i need to get the data from Jan 1, 2021 to Sep 28 2021 ( which is 270 days from jan 1 2021). If i execute this query tomorrow i need to get the data from Jan 2 2021 to Sep 29 2021. jan 3, 2021 to sep 30 2021 etc....

Could you please help me with this calculation ?

Answer 1

Hi @kkran

The posting date is in the int format.

You said that the datatype of PostingDate is INT . And as Tom said, SQL Server does not add commas to numbers.Therefore you don't need to use REPLACE function.

And I don't want to calculate going back only 425 days. Because it should calculate automatically

Do you mean :
when excute query today ,the date range is between 'DateA' and 'DateA+270days' ;
when excute on tomorrow, the date range is between 'DateA+1days' and 'DateA+271days';
when excute the day after tomorrow, the date range is between 'DateA+2days' and 'DateA+272days',and so on.
If I am right,then check this query:

 SELECT * FROM YourTable  
 WHERE LTRIM(PostingDate) BETWEEN  DATEADD(DAY, -DATEDIFF(D,'DateA','Today'), CONVERT(DATE, GETDATE()))    
                              AND  DATEADD(DAY, 270, DATEADD(DAY, -DATEDIFF(D,'DateA','Today'), CONVERT(DATE, GETDATE())));  

What you need to do is to modify the two dates 'DateA' and 'Today' only once ,and then it will calculate automatically tomorrow,the day after tomorrow ......
If I assume 'Today' is March 4th,2022 and DATE A is 'Jan 1, 2021',the code will be like:

 SELECT * FROM YourTable  
 WHERE LTRIM(PostingDate) BETWEEN  DATEADD(DAY, -DATEDIFF(D,'20210101','20220304'), CONVERT(DATE, GETDATE()))    
                              AND  DATEADD(DAY, 270, DATEADD(DAY, -DATEDIFF(D,'20210101','20220304'), CONVERT(DATE, GETDATE())));  

Best regards,
LiHong

Answer 2

WHERE datecol >= convert(int, convert(char(8), dateadd(DAY, -270, sysdatetime()), 112))

Answer 3

Since your date format is 20,220,302 which is varchar, you can convert it to the date type using:

DECLARE @PostingDate varchar(10) = '20,220,302';
SELECT CONVERT(date, REPLACE(@PostingDate, ',', ''));

Based on your requirement, the difference day number between today and 2021-01-01 is 425. So the start date for the report should be

DATEADD(DAY, -425, CONVERT(DATE, GETDATE()))

and the end date should be

DATEADD(DAY, 270, DATEADD(DAY, -425, CONVERT(DATE, GETDATE())))

So put them together, the code should like this:

SELECT *
FROM YourTable
WHERE CONVERT(DATE, REPLACE(PostingDate, ',', '')) >= DATEADD(DAY, -425, CONVERT(DATE, GETDATE()))
AND CONVERT(DATE, REPLACE(PostingDate, ',', '')) <= DATEADD(DAY, 270, DATEADD(DAY, -425, CONVERT(DATE, GETDATE())));

Answer 4

Hi @kkran
Step 1: Calculate the date interval between 20210101 and 20220302,which as Guoxiong said should be '425'. And after 270 days there should be a interval of '155'.

SELECT CONVERT(DATE, GETDATE())AS Today,DATEDIFF(D,'20210101','20220302')AS Interval ,  
       DATEADD(DAY, -425, CONVERT(DATE, GETDATE())) AS StartDate,   
       DATEADD(DAY, -155, CONVERT(DATE, GETDATE())) AS EndDate  

Output:

Step 2: Because the datatype of PostingDate is INT and it cannot be compared with the date. So you need to modify PostingDate 's datatype when querying.Check the query below:

SELECT * FROM YourTable  
WHERE LTRIM(PostingDate)  BETWEEN  DATEADD(DAY, -425, CONVERT(DATE, GETDATE()))  AND  DATEADD(DAY, -155, CONVERT(DATE, GETDATE()))  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Just to clarify the responses.

You are apparently looking at your date fields in Excel. SQL Server does not add commas to numbers. Your dates are actually YYYYMMDD, which is a normal and supported date format of 112. You can use normal date routines on this date format (after you convert the int to a varchar) as Erland posted.

WHERE datecol >= convert(int, convert(char(8), dateadd(DAY, -270, sysdatetime()), 112))

Or

WHERE CONVERT(Date, CAST(PostingDate AS CHAR(8)), 112) >= DATEADD(DAY,-270,GETDATE())