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?
datetime
data type and the newer likedate
anddatetime2
). If it's adatetime
, it's stored as a binary value, if it has a format then it's a(n)varchar
. It be adatetime
value and have a format. - Larnudatetime
then you could simply use theMONTH()
function on it to partition by month. - Joakim Danielson