[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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,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
IT-CICA
Asked:
IT-CICA
  • 3
1 Solution
 
knightEknightCommented:
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
 
knightEknightCommented:
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
 
knightEknightCommented:
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
 
morguloCommented:
'...Inner join odt on odt.OrderDetailID = od.ID
AND o.ShipDate >= convert(datetime,''' + @StartDate + ''',101)
AND o.ShipDate <= convert(datetime,''' + @EndDate +''',101) PIVOT...'
0
 
IT-CICAAuthor Commented:
Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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