January 13, 2015 at 10:18 am
I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this
declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)
but this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month.
I am having a bit of trouble formatting this any ideas on how to accomplish this.
January 13, 2015 at 12:17 pm
Consider the conversion format 12, which prints the date as yymmdd. The first four characters of that date are what you desire. I'm not sure what you're looking for in terms of 30/60/90 days, but I think this code will help with formatting the date.
DECLARE @today DATE = GETDATE()
DECLARE @tablename30 char(9) = 'table' + SUBSTRING(CONVERT(char(6), DATEADD(mm, -1, @today), 12), 1, 4)
DECLARE @tablename60 char(9) = 'table' + SUBSTRING(CONVERT(char(6), DATEADD(mm, -2, @today), 12), 1, 4)
DECLARE @tablename90 char(9) = 'table' + SUBSTRING(CONVERT(char(6), DATEADD(mm, -3, @today), 12), 1, 4)
SELECT @tablename30 [30 Days], @tablename60 [60 Days], @tablename90 [90 Days]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply