T-sql related help needed.

Question

I have a UDF where I am generating current date like this month but I want 36 month old data but as soon as I enter my 37 month it should delete the first month data.My UDF loads data in one of the fact table where I need to show only previous month data.How to achieve this.
I did get 36 month data but the second condition is still a question mark to me.

Answer 1

Suppose
DATEADD(month,DATEDIFF(mm,0,DATEADD(mm,-36,GETDATE()))

This gives me 36 month data in my UDF but need to get below requirement in my fact table
Can you help me with a query which gives previous month data and at the same time delete current month data

Answer 2

Hi @Kritika arora

Can you help me with a query which gives previous month data and at the same time delete current month data

Not sure what you actually want. However, if it is a Table_Valued UDF, why not add a where filter to remove datas of current month.

select <column_list>  
from fn_UDF_Name()  
where date_column < dateadd(month,datediff(month,0,getdate()),0)  

Best regards,
LiHong