question

San-5300 avatar image
0 Votes"
San-5300 asked EchoLiu-msft edited

Last 2 Year data with Start Date and Month

Dear Experts,

My requirement is I need to bring the data from Src (SrcCoulmn: DataTime) for the Last 2 years based on Today's date with the start date/Month is 01-Apr-YYYY (Last year).

Eg: If Today's Date (18-Aug-2021). I need data from 01-April-2020 till Today's Date

How this can be done in Tsql and in SSIS.

Much Appricated.

Cheers
San

sql-server-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ San-5300,

Welcome to the microsoft TSQL Q&A forum!

Please try:

 SELECT Src  FROM yourtable
 WHERE Src BETWEEN CAST(DATEPART(yyyy,GETDATE())-1 AS VARCHAR)+
 CAST('-04-01'AS VARCHAR) AND GETDATE()

In addition, we recommend that you post questions on the TSQL and SSIS forums so that you can get more professional help.


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

That can be done with a little "calculation" and some conversion to get the dates for filtering:

 declare @today date = getdate();
 declare @fiscalStart date;
    
 set @fiscalStart = convert(date, convert(varchar(4), year(@today) - 1) + '-04-01')
    
 select @fiscalStart, @today
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.