0
votes

I'm trying to partition a table by month say Jan, Feb, Mar. The column through which I'll partition is a datetime type column with an ISO Format ('20190101', '20190201', etc).

For example, I have sales data for jan, feb, mar. I'd like the data to be partitioned by daily partitioned . ('20190101', '20190201','20190301' etc)

E.X:

Jan, Feb, Mar etc. Also I would like to keep the data less so I would like to delete daily day wise data keeping only 1 month data maximum, for example I will create jan 31 , feb 28 , mar 31, apr 30. How do I manage partition dynamically as some month is 31 days some are 28 days and 30 days. Also I need to retain only one month data for example if its, 1st of sep then I need to keep aug 31 days data, and can delete 31st jul day data now on 2nd sep I can delete 1st august data so I need to delete daily data and keep only 30 days data .

My question is: is it even possible? If it is, how an I automate the process using SSIS?

2
"The Column through which I'll partition is a datetime type column with an ISO Format" Date and time data types in SQL Server have no format (this is true for the older datetime data type and the newer like date and datetime2). If it's a datetime, it's stored as a binary value, if it has a format then it's a (n)varchar. It be a datetime value and have a format. - Larnu
yes i can change to datetime2 and how to dynamically do this - sandeep
I didn't say anything about changing the data type. - Larnu
Your question is very confusing, if you have a column that is of type datetime then you could simply use the MONTH() function on it to partition by month. - Joakim Danielson
How to manage months like some months are 28days , 31 days and 30 days? - sandeep

2 Answers

0
votes

You may try this. As you want to remove previous 30 days data from current date or any specific date, so you can easily calculate the date range by subtracting 30 days from the current date.

For 30 days

--- Instead of getdate() you may use any of your date column for filter
delete from yourtable where yourdate < DATEADD( day, -30, getdate())   ---- here on place of 30 you may use any days you want to delete

For 1 month

--- Instead of getdate() you may use any of your date column for filter
delete from yourtable where yourdate < DATEADD( month, -1, getdate())   ---- here on place of 30 you may use any days you want to delete

0
votes

Instead of having your partition boundaries be the end of the month, have them be the beginning. That is, do something like:

[2019-01-01, 2019-02-01),
[2019-02-01, 2019-03-01),
[2019-03-01, 2019-04-01),
[2019-04-01, 2019-05-01),
[2019-05-01, 2019-06-01),
etc

That is, the left-hand boundary is in the partition and the right-hand boundary isn't. If you're using actual Partitioning, you'd define your partition function as a "boundary left" function. See the documentation for more details.