June 27, 2011 at 10:04 pm
Hi
I am trying to create a derived column using the following case statement, but it is not working for me
case DATEPART( month, GETDATE() ) when 12 then DATEPART( year, GETDATE() )-1 else DATEPART( yyyy, GETDATE() ) end
I originally did this as a select statement and it produced what i wanted i changed it to expression but i don't seem to have it right
Is any one able to offer any ideas what is wrong with my statement
Cheers
Nigel
June 28, 2011 at 4:23 am
Can you please be more specific on the logic that you want to implement with this case statement ...
June 28, 2011 at 6:28 am
nigelc (6/27/2011)case DATEPART( month, GETDATE() ) when 12 then DATEPART( year, GETDATE() )-1 else DATEPART( yyyy, GETDATE() ) end
Are you trying to get the current month and year, except when it's December then you get last year?
If your data source is (e.g. OLE DB Source) from a SQL database, you can keep your current T-SQL logic in your SELECT and add the column to the data flow that way.
June 28, 2011 at 1:12 pm
Hi
SSC-Enthusiastic you are correct in interpreting what i am trying to do I plan to run the package on the first day of the month.
the problem i have with with running the T-sql over the output is that this load will be used to insert into the same table each month, so i couldn't do an update to the whole table
Cheers
Nigel
June 28, 2011 at 1:45 pm
What values do you expect to receive when running a query against that column, let's say now and 6 month from now?
Your computed column definition is based on getdate() so I expect you're looking for a the result of that calculation at runtime. Why not add this query to the related SELECT statement? This statement seems to be completely independent of the data stored in the table.
I might also have completely understood it wrong... If you're trying to apply this calculation to the date the row was inserted (or updated), you'd need to reference a column of the table.
Example:
case DATEPART( month, myTable.DateInserted ) when 12 then DATEPART( year, myTable.DateInserted )-1 else DATEPART( yyyy, myTable.DateInserted ) end
June 28, 2011 at 1:53 pm
Thats a good solution. IE adding the date part as part of the select statement
This extract would be run on the first day of the month for a datawarehouse type load so i just want to indicate which year/Month that it relates to which is why i have used the getdate function. So in 6 months time when i run this i want it to reflect the year and Month-1 at that time
Thanks for your help
Nigel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply