select dates forward from month... 1

[burnside]

i want to count how many records where dealdeldate is in the current month and how many are forward of the current month

strsql2 = "SELECT Count(tbl_deal.dealid) AS countfwdeal"
strsql2 = strsql2 & " FROM tbl_deal"
strsql2 = strsql2 & " WHERE (DATEPART(mm,dealdeldate) >= DATEPART(mm,GETDATE()) AND DATEPART(yyyy,dealdeldate) >= DATEPART(yyyy,GETDATE()))

 

[PHV]

how many records where dealdeldate is in the current month
SELECT Count(*) AS countfwdeal FROM tbl_deal WHERE 100*Year(dealdeldate)+Month(dealdeldate)=100*Year(Date())+Month(Date())
how many records are forward of the current month
SELECT Count(*) AS countfwdeal FROM tbl_deal WHERE 100*Year(dealdeldate)+Month(dealdeldate)>100*Year(Date())+Month(Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[burnside]

thanks for yur reply PHV am gettin this error for where dealdeldate is in the current month

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '())+ Month(Date())' at line 1
/dfasp2/dealanalysis.asp, line 42

 

[burnside]

format of dealdel date is yyyy/mm/dd and im using mysql

 

[PHV]

Ah, MySQL ...
My guess:
SELECT Count(*) AS countfwdeal FROM tbl_deal WHERE 100*Year(dealdeldate)+Month(dealdeldate)=100*Year(GetDate())+Month(GetDate())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[burnside]

am now gettin this error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '())+Month(GetDate())' at line 1
/dfasp2/dealanalysis.asp, line 42

thanks PHV am gettin closer have posted in the mysql forum

 

[r937]

mysql? that syntax is way different, eh

select sum(
       case when dealdeldate 
       between 
         date_add( current_date
            , interval 1 
            - dayofmonth(current_date) day )
       and 
         date_add( 
         date_add( current_date
            , interval 1 
            - dayofmonth(current_date) day )
            , interval 1 month )
          then 1 else 0 end  
           ) as count_currentmonth
     , sum(
       case when dealdeldate
        >= 
         date_add( 
         date_add( current_date
            , interval 1 
            - dayofmonth(current_date) day )
            , interval 1 month )
          then 1 else 0 end  
           ) as count_forward
  from tbl_deal  
 where dealdeldate
        >= 
         date_add( current_date
            , interval 1 
            - dayofmonth(current_date) day )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)