Microsoft Excel - Passing date parameter vba - Asked By farrukh on 25-Feb-12 10:58 AM

Hi All,


I need some help to passing the date parameter in Sql below query the date parameter which iam pasing is not working please see in the defined variable what i am doing wrong and not getting data on excel sheet?



Sub Tester(dAsOfDate As Date, dsEndDate As Date)
    Dim wSheet As Worksheet
    Dim wProd As Worksheet
    Dim dStartdate As Date, indate As Date
    Dim dEnddate As Date
    Dim sql As String
    Dim sql1 As String
    Dim sPotRangeUL As String
  
   dStartdate = CDate(dAsOfDate)
   dEnddate = CDate(dsEndDate)
    
  sPotRangeUL= "A2"

    '*******Get input data
Dim Str_dStartdate As String
Dim Str_dEnddate As String
Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))

Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))

'      '------------------------------------------Query-----------------------------------'
  sql = "  SELECT     DATEADD(mm, DATEDIFF(m,1,v2.START_DATETIME),0) as v2, " & _
    "        ISNULL(SUM(v1.Value_1),0)AS Value_1 " & _
" FROM       (select DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0) as START_DATETIME, " & _
 "         ISNULL(SUM(v1.Value_1),0) AS Value_1 " & _
 "          from Table  v1 " & _
  "          where  v1.ITEM_TYPE='COMP " & _
  "          AND  v1.ITEM_NAME IN('Test') " & _
  "                    AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) >= " &
Str_dStartdate & " " & _
  "          AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) <= " & Str_dEnddate & " " & _
  "          group by DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0)) v2 left outer join " & _
  "         CUST_TOTALS_DAY_BHIT_BADHRA_ENIPAK v1 on " & _
  "           CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0),101)) " & _
   "          <= CONVERT(DATETIME,CONVERT(VARCHAR,v2.START_DATETIME,101)) " & _
" WHERE          v1.ITEM_TYPE='COMP' " & _
"          AND  v1.ITEM_NAME IN('Test')   " & _
"          AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) <= " & Str_dEnddate & " " & _
" group by    v2.START_DATETIME "

Call dataGetter.GetOperFieldRead(dStartdate, dEnddate, sql, 43, sPotRangeUL, 0, 100)  ''' this will go to record sets to fill the data in the sheet
end sub 



Thanks

D Company replied to farrukh on 25-Feb-12 11:04 AM
Make sure that your input date string go to sql in proper format, it accept in yyyy/mm/dd format. put a breakpoint and check,second run the same query to your db and see if it returns any data.

hope it helps
Regards
D
farrukh replied to D Company on 25-Feb-12 11:15 AM
It is going in that format 201212     while checking with break point,  How can i change the below to the format   "yyyy/mm/dd"  ?



Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))

Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))


Thanks
farrukh
D Company replied to farrukh on 25-Feb-12 01:33 PM
keep variables in string and save
like year=2012/
month=02/
date=17
and add 2012/02/17 , it will work
regrds
D
Sandeep Mittal replied to farrukh on 25-Feb-12 10:56 PM
Replace
CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101))
With
YEAR(v1.START_DATETIME) * 100 + MONTH(v1.START_DATETIME)

This would give you the format as same you are passing the value and so value would compared in same format

Check this for your reference

SELECT YEAR(GETDATE()) * 100 + MONTH(GETDATE())

OUPTUT : 201202
farrukh replied to Sandeep Mittal on 26-Feb-12 12:30 AM
sandeep,


Thank you now the results in the excel sheet is coming but problem is i am providing the range 20009
To 201212 

Checking from break point and paste the query on sql server the results are coming since start to end of the date the date parameter is not restricting the query to run between
 
20009 To 201212  it is fetching all the records of that query any idea ?


Thank you
Farrukh

farrukh replied to Sandeep Mittal on 26-Feb-12 06:12 AM

Thank you all for your support passing below to query solve the issue



Str_dStartdate = Str(Month(dStartdate)) & "/1/" & Str(Year(dStartdate))

Str_dEnddate = Str(Month(dEnddate)) & "/1/" & Str(Year(dEnddate))




Thanks
farrukh
Sandeep Mittal replied to farrukh on 26-Feb-12 07:36 AM
To solve this, you can simply

REPLACE
Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))
Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))
WITH
Str_dStartdate = Trim(Str(Year(dStartdate)*100+Month(dStartdate)))
Str_dEnddate = Trim(Str(Year(dEnddate)*100+Month(dEnddate))
farrukh replied to Sandeep Mittal on 26-Feb-12 12:57 PM
Sandeep ,

Great !

It also worked as you modified the query and passing parameters :-)

Thank you

Farrukh
Sandeep Mittal replied to farrukh on 26-Feb-12 10:02 PM
You are welcome.