IT-CICA
asked on
Date Parameters for dynamic PIVOT where clause
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,OrderDat e,ShipDate , ' + @cols +
' FROM(
Select
OP.OrderID
,OP.ProductID
,convert(varchar,O.OrderDa te,101) as OrderDate
,convert(varchar,O.ShipDat e,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)
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,OrderDat
' FROM(
Select
OP.OrderID
,OP.ProductID
,convert(varchar,O.OrderDa
,convert(varchar,O.ShipDat
,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
AND convert(varchar,o.ShipDate
(max(TA) for ST in (' + @PIVOTcols + ')) as PivotTable '
PRINT (@SQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
04/01/2010 > 03/01/2011
Format 121 puts the year first, resolving this issue
2011-03-01 > 2010-04-01
'...Inner join odt on odt.OrderDetailID = od.ID
AND o.ShipDate >= convert(datetime,''' + @StartDate + ''',101)
AND o.ShipDate <= convert(datetime,''' + @EndDate +''',101) PIVOT...'
AND o.ShipDate >= convert(datetime,''' + @StartDate + ''',101)
AND o.ShipDate <= convert(datetime,''' + @EndDate +''',101) PIVOT...'
ASKER
Thanks.
AND convert(varchar(10),o.Ship
AND convert(varchar(10),o.Ship