Excel ODBC QUERY with Declared @EOMONTH

I have a query that runs fine when I set the dates to static value it works fine, but when I set values to a declared variable it does not work. This query is run within Excel via an ODBC.
Is this even possible?

Here is my sql statement.
Declare @eom as Datetime
Declare @boy as DateTime  

set @eom = (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)) 
Set @boy = (DATEADD(Year, DATEDIFF(year, 0, GETDATE()), 0))



Select  Row_Number() over (Partition by StmnDetail order by stmndetail) as RowNum,
Sd1.StmnDetail,m1.matters,m1.clientid,p1.professionals,SD1.PaidDate,S1.StmnDate,P1.ProfName,m1.Matterid,m1.areaoflaw,
			(CASE WHEN LedgerType = 'S' 
										THEN Paid - WO 
											ELSE - (Billed - Paid) 
												END ) AS [Cash Received]--, [Firm Origination]
FROM        dbo.StmnDetail AS SD1 LEFT OUTER JOIN
			dbo.Professionals AS P1 ON SD1.Professionals = P1.Professionals INNER JOIN
			dbo.StmnLedger AS S1 ON SD1.StmnLedger = S1.StmnLedger INNER JOIN
			dbo.Matters AS M1 ON S1.matters = M1.Matters LEFT OUTER JOIN 
            MatterTypes as MT1 ON M1.MatterType = MT1.MatterTypesDesc
			--left outer join  CV_temp_Tbl CV1 on cv1.matters = m1.matters
			--left outer join MattersProfessionals as MP1 on m1.matters = mp1.matters 
			--Inner join Professionals as p2 on MP1.Professionals = p2.professionals


WHERE     (SD1.CompType = 'F') AND  (NOT (M1.MatterType LIKE '%Admin%')) AND (SD1.PaidDate >= @boy and SD1.PaidDate <= @eom) 

Open in new window

LVL 26
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
 
ZberteocCommented:
Put everything into a stored procedure, with the declare and variable use, and execute that from excel.
0
 
JimFiveCommented:
Your parameters for DateAdd are in the wrong order it is:  DateAdd(interval, number, date), Also DateDiff requires two date parameters it looks like you want end of the previous month and beginning of the current year, so:

Set @EOM = DateAdd(day, -Day(GetDate()), GetDate())
Set @BOY = DateAdd(day, -DatePart(dy, GetDate())-1, GetDate())

Open in new window

The  -1 is so that you get the beginning of the year instead of the end of last year.
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
So standard SQL functions will not pass in Excel ODBC ?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ZberteocCommented:
Try to run this:
Select  Row_Number() over (Partition by StmnDetail order by stmndetail) as RowNum,
Sd1.StmnDetail,m1.matters,m1.clientid,p1.professionals,SD1.PaidDate,S1.StmnDate,P1.ProfName,m1.Matterid,m1.areaoflaw,
			(CASE WHEN LedgerType = 'S' 
										THEN Paid - WO 
											ELSE - (Billed - Paid) 
												END ) AS [Cash Received]--, [Firm Origination]
FROM        dbo.StmnDetail AS SD1 LEFT OUTER JOIN
			dbo.Professionals AS P1 ON SD1.Professionals = P1.Professionals INNER JOIN
			dbo.StmnLedger AS S1 ON SD1.StmnLedger = S1.StmnLedger INNER JOIN
			dbo.Matters AS M1 ON S1.matters = M1.Matters LEFT OUTER JOIN 
            MatterTypes as MT1 ON M1.MatterType = MT1.MatterTypesDesc
			--left outer join  CV_temp_Tbl CV1 on cv1.matters = m1.matters
			--left outer join MattersProfessionals as MP1 on m1.matters = mp1.matters 
			--Inner join Professionals as p2 on MP1.Professionals = p2.professionals


WHERE     (SD1.CompType = 'F') AND  (NOT (M1.MatterType LIKE '%Admin%')) AND (SD1.PaidDate >= (DATEADD(Year, DATEDIFF(year, 0, GETDATE()), 0)) and SD1.PaidDate <= (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)))

Open in new window

0
 
yo_beeDirector of Information TechnologyAuthor Commented:
This SQL Statement I posted  is only a snippet of the whole statement.
This Statement consist of 6 CTE to get to the final results.
The part I posted is the first of the 6 CTE.  When I change the Declare variable to have the DateAdd function within the where statement the query runs for an extremely long time.

So that is not an option.
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
That was perfect.  Thank you for all your help and patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.