• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

How to setup month and year in stored procedure to react to current date

The title is a bit misleading, but here we go.  I have stored procedures that are inside of DTS packages (soon to be moved to SSIS packages) where it looks to a date to identify where to load certain data points in the final table.  I have supplied the code below to help understand the scenario.  Really kind of blowing out columnar data to tabular data within SQL.

Anyway, here is an example statement: exec sp_logility_load_demand_lvl1 '2009-12-01'

So as you can see, the last part of the call to the stored procedure is the date 'yyyy-mm-dd', but the day value is ALWAYS to be defaulted to 01, the first day of the current month.  I would like to somehow make this call statement in a fashion that would simply check the system date to automatically load the correct values for current month and year.

I am sure it can be done, but I am new to SQL, and dates seem to elude me.  

Help?!?

Thanks
USE [demand_plan]
GO
/****** Object:  StoredProcedure [dbo].[sp_logility_load_demand_lvl1]    Script Date: 12/28/2009 13:11:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE  procedure [dbo].[sp_logility_load_demand_lvl1]
	@target_month_date smalldatetime
as

truncate table rep_logility_demand_lvl1

declare 
	@dmnd01 int,
	@dmnd02 int,
	@dmnd03 int,
	@dmnd04 int,
	@dmnd05 int,
	@dmnd06 int,
	@dmnd07 int,
	@dmnd08 int,
	@dmnd09 int,
	@dmnd10 int,

	@dmnd11 int,
	@dmnd12 int,
	@dmnd13 int,
	@dmnd14 int,
	@dmnd15 int,
	@dmnd16 int,
	@dmnd17 int,
	@dmnd18 int,
	@dmnd19 int,
	@dmnd20 int,

	@dmnd21 int,
	@dmnd22 int,
	@dmnd23 int,
	@dmnd24 int,
	@dmnd25 int,
	@dmnd26 int,
	@dmnd27 int,
	@dmnd28 int,
	@dmnd29 int,
	@dmnd30 int,

	@dmnd31 int,
	@dmnd32 int,
	@dmnd33 int,
	@dmnd34 int,
	@dmnd35 int,
	@dmnd36 int


set @dmnd01 = year(dateadd(month, -1, @target_month_date))*100 + month(dateadd(month, -1, @target_month_date))
set @dmnd02 = year(dateadd(month, -2, @target_month_date))*100 + month(dateadd(month, -2, @target_month_date))
set @dmnd03 = year(dateadd(month, -3, @target_month_date))*100 + month(dateadd(month, -3, @target_month_date))
set @dmnd04 = year(dateadd(month, -4, @target_month_date))*100 + month(dateadd(month, -4, @target_month_date))
set @dmnd05 = year(dateadd(month, -5, @target_month_date))*100 + month(dateadd(month, -5, @target_month_date))
set @dmnd06 = year(dateadd(month, -6, @target_month_date))*100 + month(dateadd(month, -6, @target_month_date))
set @dmnd07 = year(dateadd(month, -7, @target_month_date))*100 + month(dateadd(month, -7, @target_month_date))
set @dmnd08 = year(dateadd(month, -8, @target_month_date))*100 + month(dateadd(month, -8, @target_month_date))
set @dmnd09 = year(dateadd(month, -9, @target_month_date))*100 + month(dateadd(month, -9, @target_month_date))
set @dmnd10 = year(dateadd(month, -10, @target_month_date))*100 + month(dateadd(month, -10, @target_month_date))

set @dmnd11 = year(dateadd(month, -11, @target_month_date))*100 + month(dateadd(month, -11, @target_month_date))
set @dmnd12 = year(dateadd(month, -12, @target_month_date))*100 + month(dateadd(month, -12, @target_month_date))
set @dmnd13 = year(dateadd(month, -13, @target_month_date))*100 + month(dateadd(month, -13, @target_month_date))
set @dmnd14 = year(dateadd(month, -14, @target_month_date))*100 + month(dateadd(month, -14, @target_month_date))
set @dmnd15 = year(dateadd(month, -15, @target_month_date))*100 + month(dateadd(month, -15, @target_month_date))
set @dmnd16 = year(dateadd(month, -16, @target_month_date))*100 + month(dateadd(month, -16, @target_month_date))
set @dmnd17 = year(dateadd(month, -17, @target_month_date))*100 + month(dateadd(month, -17, @target_month_date))
set @dmnd18 = year(dateadd(month, -18, @target_month_date))*100 + month(dateadd(month, -18, @target_month_date))
set @dmnd19 = year(dateadd(month, -19, @target_month_date))*100 + month(dateadd(month, -19, @target_month_date))
set @dmnd20 = year(dateadd(month, -20, @target_month_date))*100 + month(dateadd(month, -20, @target_month_date))

set @dmnd21 = year(dateadd(month, -21, @target_month_date))*100 + month(dateadd(month, -21, @target_month_date))
set @dmnd22 = year(dateadd(month, -22, @target_month_date))*100 + month(dateadd(month, -22, @target_month_date))
set @dmnd23 = year(dateadd(month, -23, @target_month_date))*100 + month(dateadd(month, -23, @target_month_date))
set @dmnd24 = year(dateadd(month, -24, @target_month_date))*100 + month(dateadd(month, -24, @target_month_date))
set @dmnd25 = year(dateadd(month, -25, @target_month_date))*100 + month(dateadd(month, -25, @target_month_date))
set @dmnd26 = year(dateadd(month, -26, @target_month_date))*100 + month(dateadd(month, -26, @target_month_date))
set @dmnd27 = year(dateadd(month, -27, @target_month_date))*100 + month(dateadd(month, -27, @target_month_date))
set @dmnd28 = year(dateadd(month, -28, @target_month_date))*100 + month(dateadd(month, -28, @target_month_date))
set @dmnd29 = year(dateadd(month, -29, @target_month_date))*100 + month(dateadd(month, -29, @target_month_date))
set @dmnd30 = year(dateadd(month, -30, @target_month_date))*100 + month(dateadd(month, -30, @target_month_date))

set @dmnd31 = year(dateadd(month, -31, @target_month_date))*100 + month(dateadd(month, -31, @target_month_date))
set @dmnd32 = year(dateadd(month, -32, @target_month_date))*100 + month(dateadd(month, -32, @target_month_date))
set @dmnd33 = year(dateadd(month, -33, @target_month_date))*100 + month(dateadd(month, -33, @target_month_date))
set @dmnd34 = year(dateadd(month, -34, @target_month_date))*100 + month(dateadd(month, -34, @target_month_date))
set @dmnd35 = year(dateadd(month, -35, @target_month_date))*100 + month(dateadd(month, -35, @target_month_date))
set @dmnd36 = year(dateadd(month, -36, @target_month_date))*100 + month(dateadd(month, -36, @target_month_date))


insert into rep_logility_demand_lvl1(target_date, FCST_ID, 
	DMND01, DMND02, DMND03, DMND04, DMND05, DMND06, 
	DMND07, DMND08, DMND09, DMND10, DMND11, DMND12, 
	DMND13, DMND14, DMND15, DMND16, DMND17, DMND18, 
	DMND19, DMND20, DMND21, DMND22, DMND23, DMND24,
	DMND25, DMND26, DMND27, DMND28, DMND29, DMND30, 
	DMND31, DMND32, DMND33, DMND34, DMND35, DMND36 
	)
	select @target_month_date,
		FCST_ID,
		sum(case when FCST_YR_PRD = @dmnd01 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd02 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd03 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd04 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd05 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd06 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd07 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd08 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd09 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd10 then DMD_ACTL_QTY else 0 end),

		sum(case when FCST_YR_PRD = @dmnd11 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd12 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd13 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd14 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd15 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd16 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd17 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd18 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd19 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd20 then DMD_ACTL_QTY else 0 end),

		sum(case when FCST_YR_PRD = @dmnd21 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd22 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd23 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd24 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd25 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd26 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd27 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd28 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd29 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd30 then DMD_ACTL_QTY else 0 end),

		sum(case when FCST_YR_PRD = @dmnd31 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd32 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd33 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd34 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd35 then DMD_ACTL_QTY else 0 end),
		sum(case when FCST_YR_PRD = @dmnd36 then DMD_ACTL_QTY else 0 end)
		from logex_demand_36_lvl1
		group by FCST_ID

Open in new window

0
dabdowb
Asked:
dabdowb
  • 5
  • 4
  • 3
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
this wull return the current start of month value
SELECT DATEADD(month, DATEDIFF(month, '', getdate() ) , '')
you can set the above value to  that parameter inside the sp
0
 
dabdowbAuthor Commented:
I get this error when I try this:

Msg 174, Level 15, State 1, Line 1
The datediff function requires 3 argument(s).
 
Also, are you saying I would put this statement inside the sp itself?  If so, where?  I mean, I know it needs to be assigned to target_month_date after it is declared type smalldatetime, but how would I assign the result of a SELECT statement to that?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Not sure how you tried that, please copy and paste the exact code  that will work
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
USE [demand_plan]
GO
/****** Object:  StoredProcedure [dbo].[sp_logility_load_demand_lvl1]    Script Date: 12/28/2009 13:11:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE  procedure [dbo].[sp_logility_load_demand_lvl1]
     
as

truncate table rep_logility_demand_lvl1

declare
      @dmnd01 int,
      @dmnd02 int,
      @dmnd03 int,
      @dmnd04 int,
      @dmnd05 int,
      @dmnd06 int,
      @dmnd07 int,
      @dmnd08 int,
      @dmnd09 int,
      @dmnd10 int,

      @dmnd11 int,
      @dmnd12 int,
      @dmnd13 int,
      @dmnd14 int,
      @dmnd15 int,
      @dmnd16 int,
      @dmnd17 int,
      @dmnd18 int,
      @dmnd19 int,
      @dmnd20 int,

      @dmnd21 int,
      @dmnd22 int,
      @dmnd23 int,
      @dmnd24 int,
      @dmnd25 int,
      @dmnd26 int,
      @dmnd27 int,
      @dmnd28 int,
      @dmnd29 int,
      @dmnd30 int,

      @dmnd31 int,
      @dmnd32 int,
      @dmnd33 int,
      @dmnd34 int,
      @dmnd35 int,
      @dmnd36 int

declare  @target_month_date smalldatetime

SET  @target_month_date = DATEADD(month, DATEDIFF(month, '', getdate() ) , '')

set @dmnd01 = year(dateadd(month, -1, @target_month_date))*100 + month(dateadd(month, -1, @target_month_date))
set @dmnd02 = year(dateadd(month, -2, @target_month_date))*100 + month(dateadd(month, -2, @target_month_date))
set @dmnd03 = year(dateadd(month, -3, @target_month_date))*100 + month(dateadd(month, -3, @target_month_date))
set @dmnd04 = year(dateadd(month, -4, @target_month_date))*100 + month(dateadd(month, -4, @target_month_date))
set @dmnd05 = year(dateadd(month, -5, @target_month_date))*100 + month(dateadd(month, -5, @target_month_date))
set @dmnd06 = year(dateadd(month, -6, @target_month_date))*100 + month(dateadd(month, -6, @target_month_date))
set @dmnd07 = year(dateadd(month, -7, @target_month_date))*100 + month(dateadd(month, -7, @target_month_date))
set @dmnd08 = year(dateadd(month, -8, @target_month_date))*100 + month(dateadd(month, -8, @target_month_date))
set @dmnd09 = year(dateadd(month, -9, @target_month_date))*100 + month(dateadd(month, -9, @target_month_date))
set @dmnd10 = year(dateadd(month, -10, @target_month_date))*100 + month(dateadd(month, -10, @target_month_date))

set @dmnd11 = year(dateadd(month, -11, @target_month_date))*100 + month(dateadd(month, -11, @target_month_date))
set @dmnd12 = year(dateadd(month, -12, @target_month_date))*100 + month(dateadd(month, -12, @target_month_date))
set @dmnd13 = year(dateadd(month, -13, @target_month_date))*100 + month(dateadd(month, -13, @target_month_date))
set @dmnd14 = year(dateadd(month, -14, @target_month_date))*100 + month(dateadd(month, -14, @target_month_date))
set @dmnd15 = year(dateadd(month, -15, @target_month_date))*100 + month(dateadd(month, -15, @target_month_date))
set @dmnd16 = year(dateadd(month, -16, @target_month_date))*100 + month(dateadd(month, -16, @target_month_date))
set @dmnd17 = year(dateadd(month, -17, @target_month_date))*100 + month(dateadd(month, -17, @target_month_date))
set @dmnd18 = year(dateadd(month, -18, @target_month_date))*100 + month(dateadd(month, -18, @target_month_date))
set @dmnd19 = year(dateadd(month, -19, @target_month_date))*100 + month(dateadd(month, -19, @target_month_date))
set @dmnd20 = year(dateadd(month, -20, @target_month_date))*100 + month(dateadd(month, -20, @target_month_date))

set @dmnd21 = year(dateadd(month, -21, @target_month_date))*100 + month(dateadd(month, -21, @target_month_date))
set @dmnd22 = year(dateadd(month, -22, @target_month_date))*100 + month(dateadd(month, -22, @target_month_date))
set @dmnd23 = year(dateadd(month, -23, @target_month_date))*100 + month(dateadd(month, -23, @target_month_date))
set @dmnd24 = year(dateadd(month, -24, @target_month_date))*100 + month(dateadd(month, -24, @target_month_date))
set @dmnd25 = year(dateadd(month, -25, @target_month_date))*100 + month(dateadd(month, -25, @target_month_date))
set @dmnd26 = year(dateadd(month, -26, @target_month_date))*100 + month(dateadd(month, -26, @target_month_date))
set @dmnd27 = year(dateadd(month, -27, @target_month_date))*100 + month(dateadd(month, -27, @target_month_date))
set @dmnd28 = year(dateadd(month, -28, @target_month_date))*100 + month(dateadd(month, -28, @target_month_date))
set @dmnd29 = year(dateadd(month, -29, @target_month_date))*100 + month(dateadd(month, -29, @target_month_date))
set @dmnd30 = year(dateadd(month, -30, @target_month_date))*100 + month(dateadd(month, -30, @target_month_date))

set @dmnd31 = year(dateadd(month, -31, @target_month_date))*100 + month(dateadd(month, -31, @target_month_date))
set @dmnd32 = year(dateadd(month, -32, @target_month_date))*100 + month(dateadd(month, -32, @target_month_date))
set @dmnd33 = year(dateadd(month, -33, @target_month_date))*100 + month(dateadd(month, -33, @target_month_date))
set @dmnd34 = year(dateadd(month, -34, @target_month_date))*100 + month(dateadd(month, -34, @target_month_date))
set @dmnd35 = year(dateadd(month, -35, @target_month_date))*100 + month(dateadd(month, -35, @target_month_date))
set @dmnd36 = year(dateadd(month, -36, @target_month_date))*100 + month(dateadd(month, -36, @target_month_date))


insert into rep_logility_demand_lvl1(target_date, FCST_ID,
      DMND01, DMND02, DMND03, DMND04, DMND05, DMND06,
      DMND07, DMND08, DMND09, DMND10, DMND11, DMND12,
      DMND13, DMND14, DMND15, DMND16, DMND17, DMND18,
      DMND19, DMND20, DMND21, DMND22, DMND23, DMND24,
      DMND25, DMND26, DMND27, DMND28, DMND29, DMND30,
      DMND31, DMND32, DMND33, DMND34, DMND35, DMND36
      )
      select @target_month_date,
            FCST_ID,
            sum(case when FCST_YR_PRD = @dmnd01 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd02 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd03 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd04 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd05 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd06 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd07 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd08 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd09 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd10 then DMD_ACTL_QTY else 0 end),

            sum(case when FCST_YR_PRD = @dmnd11 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd12 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd13 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd14 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd15 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd16 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd17 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd18 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd19 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd20 then DMD_ACTL_QTY else 0 end),

            sum(case when FCST_YR_PRD = @dmnd21 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd22 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd23 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd24 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd25 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd26 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd27 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd28 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd29 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd30 then DMD_ACTL_QTY else 0 end),

            sum(case when FCST_YR_PRD = @dmnd31 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd32 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd33 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd34 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd35 then DMD_ACTL_QTY else 0 end),
            sum(case when FCST_YR_PRD = @dmnd36 then DMD_ACTL_QTY else 0 end)
            from logex_demand_36_lvl1
            group by FCST_ID
0
 
Kevin CrossChief Technology OfficerCommented:
Aneesh's answer should have worked for you.  From the question, I gathered you were trying to pass the beginning of the month to the stored procedure and NOT alter the stored procedure itself, but I may be incorrect.  Take Aneesh's solution and try this:

declare @target_date smalldatetime
set @target_date = dateadd(month, datediff(month, '', getdate()), '');
exec sp_logility_load_demand_lvl1 @target_date;
0
 
Kevin CrossChief Technology OfficerCommented:
BTW, this will work until we get to 5 digit years. :)


set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date)), 112)
0
 
Kevin CrossChief Technology OfficerCommented:
Type-o:

set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date), 112)

Copy and pasted extra ) previously.  Should just be one.  Much reduced code than had previously.  What it does is add (subtract) the number of months you want and then get date as "200911" for example.  This will convert to INT 200911 by setting as value of INT variable @dmnd01.
0
 
dabdowbAuthor Commented:
It was hard for me to read the original text, and trying to copy/paste didn't work, so the error came from putting a double quote in the place of to single quote marks next to each other.  As soon as I changed that it fired up like a charm...very nice!!
As it is currently set, I don't have the date coming from inside the stored procedure, it is coming from the call, BUT, I didn't write the code and never have liked it, so putting it in the code is just fine by me.
I just tested it with great success. Very nice!
0
 
dabdowbAuthor Commented:
mwvisa1,
what is the 112 in your proposed formula for the main body of the stored procedure?
really, I don't quite get the date stuff in general.  I have come to learn that SQL makes you dateadd and datediff to fluctuate off of the getdate() feature (why can't it just be like in Access or VBA...date() and DONE!!!...same company you think they could keep it simple...whatever).
Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?
Thanks,
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, I meant to explain a little better.
The CONVERT() function can be used to format the datetime object as a string.  112 is the code for the ISO format YYYYMMDD which today would be 20091228 for example.  Using CONVERT(), as you can see the first parameter is data type and since VARCHAR can be given a specific length I have chosen 6 which will thus take the first 6 characters 200912.

Books Online Reference:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Hope that helps.
0
 
Kevin CrossChief Technology OfficerCommented:
And glad you found Aneesh's solution successful as it would be the better way to go if you can change the stored procedure if you are always wanting this run for the current month.  

@Aneesh, great work as usual, sir!

@dabdowb:
>>Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?

That is a great attitude to have; you will learn much more that way!  Also to answer your question on the second portion as I see you wanted to understand Aneesh's solution as well, that formula is simply this:

DATEDIFF( month, '', getdate() )
'' or 0 casts to '1900-01-01' which is the base date for SQL Server datetime.  Therefore, you are getting the number of months from date 0 to the current date.

DATEADD( month, n, '' )
Given above, you are starting from '1900-01-01' and adding n number of months to it.  If you substitute n with the formula using datediff, you will add the number of months to get to the current month.  Since you have not specified anything further, it will remain on the 1st since that is the day which date 0 starts.  Hopefully that is clear.

Best regards and happy coding,

--isa
0
 
dabdowbAuthor Commented:
Both experts, Aneeshattingal and mwvisa1, supplied accurate results. Aneesh went an added step and showed me how to construct the code inside the stored procedure.  Mwvisa1, went the extra mile and not only explained how both results worked, but streamlined my code as well inside the stored procedure...above and beyond the question itself.  Awesome work from both so split the points equally as both were excellent resources for help.  Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now