?
Solved

Date Parameters for dynamic PIVOT where clause

Posted on 2011-03-16
5
Medium Priority
?
338 Views
Last Modified: 2012-05-11
Hi,
I have  following query where I need to pass Dates parameters to the WHERE clause.
If I input the dates to the WHERE clause as Strings, then the records don't get filtered based on the dates. If I input the dates as DateTime, I get error saying 'Conversion failed when converting datetime from character string'.

Any suggestions?
Thanks in advance:

DECLARE @StartDate as DateTime,@EndDate as DateTime,@StartDateStr as varchar,@EndDateStr as varchar,@cols NVARCHAR(2000),@PIVOTcols NVARCHAR(2000),
@SQL nvarchar(max),@ProductID as varchar(max)

SET @productID = 9999
SET @StartDate = '04/11/2009'
SET @EndDate = '04/14/2009'
SET @StartDateStr = '04/11/2009'
SET @EndDatestr = '04/14/2009'


SELECT  @cols = 'A,B,C'

SELECT  @PIVOTcols = 'A,B,C'

SET  @SQL =

'Select OrderID,ProductId,OrderDate,ShipDate, '  + @cols +
' FROM(
Select  
OP.OrderID
,OP.ProductID
,convert(varchar,O.OrderDate,101) as OrderDate
,convert(varchar,O.ShipDate,101) as ShipDate
,odt.ST
,odt.TA
from OP
inner join O on OP.OrderId = O.ID
Inner join od on O.ID = od.orderid
Inner join odt on odt.OrderDetailID = od.ID
'AND convert(varchar,o.ShipDate,101) >= convert(varchar,' + @StartDate + ',101)
AND convert(varchar,o.ShipDate,101) <= convert(varchar,' + @EndDate +',101)
PIVOT
(max(TA) for ST in (' + @PIVOTcols + ')) as PivotTable '

PRINT (@SQL)
0
Comment
Question by:IT-CICA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35152154
try varchar(10) and format 121:

AND convert(varchar(10),o.ShipDate,121) >= convert(varchar(10),' + @StartDate + ',121)
AND convert(varchar(10),o.ShipDate,121) <= convert(varchar(10),' + @EndDate +',121)
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35152157
oops, i see extra quote marks ...

AND convert(varchar(10),o.ShipDate,121) >= convert(varchar(10), @StartDate, 121)
AND convert(varchar(10),o.ShipDate,121) <= convert(varchar(10), @EndDate, 121)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35152169
using format 101 the string comparisons will not work properly because (for example) april of 2010 will sort before march of 2011 because the month is first.

  04/01/2010 > 03/01/2011

Format 121 puts the year first, resolving this issue

  2011-03-01 > 2010-04-01
0
 
LVL 5

Expert Comment

by:morgulo
ID: 35152205
'...Inner join odt on odt.OrderDetailID = od.ID
AND o.ShipDate >= convert(datetime,''' + @StartDate + ''',101)
AND o.ShipDate <= convert(datetime,''' + @EndDate +''',101) PIVOT...'
0
 

Author Closing Comment

by:IT-CICA
ID: 35332783
Thanks.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question