November 8, 2011 at 6:07 am
I'm trying to write code for some Column Headings in an SSIS Package.
The following works:
SELECTCAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear',
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' YTD' AS 'YearToDate',
CAST(LEFT(UPPER(DATENAME(MONTH, GETDATE())),3) AS VARCHAR(3))+ ' ' +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'CurrentMTD'
PrevYearYearToDateCurrentMTD
20102011 YTDNOV 2011 MTD
I tried to add a LEFT Function to the following and I got an error. So far I have the following but it returns the wrong month:
SELECT UPPER(DATENAME(MONTH,MONTH(DATEADD(mm, 0, GETDATE())))) AS 'CurentMonth'
CurentMonth
JANUARY
The following Statement returns the Month Number:
SELECT CAST(MONTH(DATEADD(mm, 0, GETDATE())) AS VARCHAR(4)) AS 'CurrentMonth'
CurrentMonth
11
Any help would be greatly appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 6:36 am
It looks like I have it. Need to do Current Week & Weeks Prior.
It could be better.
SELECTCAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear',
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' YTD' AS 'YearToDate',
--CAST(LEFT(UPPER(DATENAME(MONTH, GETDATE())),3) AS VARCHAR(3))+ ' ' +
--CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'CurrentMTD',-- OR
CAST(UPPER(DATEADD(mm, 0, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Current MTD',
CAST(UPPER(DATEADD(mm, -1, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Previous Month',CAST(UPPER(DATEADD (mm, -2, GETDATE())) AS VARCHAR(4)) +
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' MTD' AS 'Two Months Prior'
Returns:
PrevYearYearToDateCurrent MTDPrevious MonthTwo Months Prior
20102011 YTDNOV 2011 MTDOCT 2011 MTDSEP 2011 MTD
Now, I'm having Trouble getting the Current Week and 4 Weeks Prior.:w00t:
I need the Weeks Starting with the Current Week Starting on Sunday Where the current Week is:
Week of NOV 6
Previous Weeks are:
Week of OCT 30
Week of OCT 23
Week of OCT 16
Week of OCT 9
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 7:05 am
Since I resolved the original problem I have moved my new question to a new post:
http://www.sqlservercentral.com/Forums/Topic1202082-338-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2011 at 7:34 am
It will not work in SSIS, Tested it, disregard.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply