I came up with a DDL with the construct as in point - 2 above. I have few questions here.
1. Is there any advantage to define a partition as in point2 above as compared to point1 defn or do they mean the same ?
2. When i try to insert a data for order_date as 150130, the insert fails saying tht invalid partition error. But, should this data not get inserted as this is a 1 month partition table ? or does it mean that only data having order_date as 150131 will be accepted ? As per my understanding, if you have a month partition, data for that defined range can be inserted and they will be grouped together as a partition in an AMP. Error should occur, if you have not defined a partition and you have not mentioned no range, unknown etc. say if the data comes for month of Jan'15, it should be inserted and kept as one partition and if one record is inserted for 150203, then it should fail.
150130 is 1915-01-30, which definitely doesn't fit in any partition :-)
#1 and #2 are exactly the same, but #1 is preferred because it's more readable.
But what's the datatype of Order_TS?
If it's a timestamp you might use it directly without cast...
I experimented yesterday and found that it was an issue as the partition was broken in between ( i had figured it out, but was just confused). In your reply, you mentioned "150130 is 1915-01-30". Is this 2015 or 1915 ?
Teradata stores DATEs using following formula: (year -1900) * 10000 + month * 100 + day
select cast(150130 as date), cast(1150130 as date);