One thing that has got me down is as follows

 

I need to extract out data from a database table where dates are stored as 17 digit characters like:

 

20100428171456013 - the key chars are the first 8 which denote YYYY MM DD

 

Basically I want to run the following SQL when the report is run:

 

SELECT A.A_ACCID,
  CASE E.A_TRDSDE
   WHEN 'B' THEN 'BUY'
   WHEN 'S' THEN 'SELL'
  END AS TRDSDE,
  C.A_CTRPTYID,
  I.A_SEDCDE,
  SUBSTRING(E.A_ADDDTETME,0, 9) AS ADDDTE,
  SUBSTRING(E.A_STTDTE,0, 9) AS STTDTE,
  SUBSTRING(E.A_ADDDTETME,9, 4) AS ADDTME,
  E.A_QTY,
  E.A_STTCCYCDE,
  E.A_PRC, 
  E.A_GRSCSD,
  E.A_CMSAMT,
  ((E.A_GRSCSD - E.A_CMSAMT) / E.A_QTY) AS NETPRC,
  E.A_INSNAM,
  ISNULL(E.A_GIVUP, 'N') AS IS_CFD, 
  'ML' AS GIVEUP_BROKER,
  ISNULL(I.A_RICCDE, 'N/A'),
  ISNULL(E.A_LCLFEE,0) AS LOCAL_FEE,
  ISNULL(E.A_LCLTAX,0) AS LOCAL_TAX
FROM T_ATH_EXE E
JOIN T_ATH_ACC A ON E.A_ACCID = A.A_ACCID
JOIN T_ATH_CTRPTY C ON C.A_CTRPTYID = E.A_CTRPTYID
JOIN T_ATH_INS I ON I.A_INSID = E.A_INSID
WHERE E.A_ADDDTETME BETWEEN '{?startDate}' AND '{?endDate}'
AND A.A_ACCID = '{?Portfolio}'

 

{?Portfolio} is a dynamic parameter which works fine as a dropdown liist of values

 

{?startDate}' and '{?endDate}' are date parameters which take the form 28/5/2010 etc

 

It is this value  that needs to be converted to the database format I require above when it runs

 

Conversely, if I specify {?startDate}' and '{?endDate}' as a string parameter and paste in values such as 20100428171456013 during run time the report runs fine

 

What is the best way of solving this

 

 

 

 

Yes I am using commands and not tables (is that a bad thing?)

 

How do I wrap the below SQL around my date parameters in the existing SQL WHERE CLAUSE

 

My parameters are of type date