Click here to Skip to main content
14,328,979 members
Rate this:
Please Sign up or sign in to vote.
See more:
Can I get assistance please I have the stored procedure below with two date parameters (@datefrom and @dateto) that is receiving from the user when s\he generate the reports on the application. The format of values when the user pass to the parameter is as follow ‘2019-01-01’ to ‘2019-02-31’, on the database the records on the column that the parameters is comparing with is as follow ‘2019-02-28 00:00:00.000’. 

My challenge now is when am running this procedure passing values of this format ‘2019-02-31’ is not pulling any record on the database but am passing values of this format ‘2019-02-28 00:00:00.000’ its pulling the records.
Can someone assists what I need to fix on the procedure?



TER PROCEDURE [dbo].[ir_prc_getAgeAnalysis] @datefrom datetime, @dateto datetime         
         
AS        
BEGIN        
 -- SET NOCOUNT ON added to prevent extra result sets from        
 -- interfering with SELECT statements.        
 --SET NOCOUNT ON;        
        
   --get all registered companies          
set dateformat dmy           
select @dateto = dateadd(dd,1,@dateto)   
  
--temp table holds status chage date from 60 to 70 for companies  
create table    #temp (appkey int,datechanged datetime,dtecreated datetime)  
insert #temp  
select a.appkey, logdte, max(b.dtecreated) from mrapplication a, mrlogitm b  
where a.appkey = b.appkey  
and b.StatCdFrom <> 70 AND b.StatCdTo = 70  
group by a.appkey,logdte  
  
  
delete dbo.irAgeAnalysis        
INSERT dbo.irAgeAnalysis (AO, DateStatus70, DateFeesPaid,DateRegistered,StatCdFrom,StatCdTo,    
Cokey,CoNam,CurrentStatus,Regnum,numBranch,CertificateDate)                                                                                                                                      
      
SELECT DISTINCT mrCompany.AO, #temp.datechanged AS DateStatus70, irEventDate.EventDate as DateFeesPaid,mrLogItm.DteCreated AS DateRegistered, mrLogItm.StatCdFrom,  
    mrLogItm.StatCdTo,mrCompany.CoKey, mrCompany.CoNam, mrApplication.StatCd AS CurrentStatus, mrCompany.NCRMrcNum,mrapplication.numBranch ,a.EventDate    
FROM         mrLogItm INNER JOIN        
                      mrApplication ON mrLogItm.AppKey = mrApplication.AppKey INNER JOIN        
                      mrCompany ON mrApplication.CoKey = mrCompany.CoKey INNER JOIN
					  irEventDate a ON mrCompany.CoKey = a.CoKey INNER JOIN
                      #temp ON mrApplication.AppKey = #temp.AppKey LEFT OUTER JOIN        
                      irEventDate ON mrCompany.CoKey = irEventDate.CoKey        
   INNER JOIN mrcobranch ON mrcobranch.COKEY = mrCompany.CoKey    
WHERE     (mrLogItm.StatCdFrom <> 80) AND (mrLogItm.StatCdTo = 80)         
AND (irEventDate.DteCreated >= @datefrom ) AND (irEventDate.DteCreated < @dateto) 
AND (irEventDate.TypeCd = 'FEESP')        
AND (a.TypeCd = 'CERTI')  
order By mrCompany.AO                  


What I have tried:

When I uncommented number 1. On the procedure under where condition it’s not pulling anything
Number 2 and 3 It’s just duplicating the records and leave other records out


TER PROCEDURE [dbo].[ir_prc_getAgeAnalysistest] 
@datefrom datetime, 
@dateto datetime    
--@datefrom  nvarchar(25), 
--@dateto  nvarchar(25)       
         
AS        
BEGIN        
 -- SET NOCOUNT ON added to prevent extra result sets from        
 -- interfering with SELECT statements.        
 --SET NOCOUNT ON;        
        
   --get all registered companies          
set dateformat dmy           
select @dateto = dateadd(dd,1,@dateto)   
  
--temp table holds status chage date from 60 to 70 for companies  
create table    #temp (appkey int,datechanged datetime,dtecreated datetime)  
insert #temp  
select a.appkey, logdte, max(b.dtecreated) from mrapplication a, mrlogitm b  
where a.appkey = b.appkey  
and b.StatCdFrom <> 70 AND b.StatCdTo = 70  
group by a.appkey,logdte  
  
  
delete dbo.irAgeAnalysis        
INSERT dbo.irAgeAnalysis (AO, DateStatus70, DateFeesPaid,DateRegistered,StatCdFrom,StatCdTo,    
Cokey,CoNam,CurrentStatus,Regnum,numBranch,CertificateDate)                                                                                                                                      
      
SELECT DISTINCT mrCompany.AO, #temp.datechanged AS DateStatus70, irEventDate.EventDate as DateFeesPaid,mrLogItm.DteCreated AS DateRegistered, mrLogItm.StatCdFrom,  
    mrLogItm.StatCdTo,mrCompany.CoKey, mrCompany.CoNam, mrApplication.StatCd AS CurrentStatus, mrCompany.NCRMrcNum,mrapplication.numBranch ,a.EventDate    
FROM         mrLogItm INNER JOIN        
                      mrApplication ON mrLogItm.AppKey = mrApplication.AppKey INNER JOIN        
                      mrCompany ON mrApplication.CoKey = mrCompany.CoKey INNER JOIN
					  irEventDate a ON mrCompany.CoKey = a.CoKey INNER JOIN
                      #temp ON mrApplication.AppKey = #temp.AppKey LEFT OUTER JOIN        
                      irEventDate ON mrCompany.CoKey = a.CoKey        
   INNER JOIN mrcobranch ON mrcobranch.COKEY = mrCompany.CoKey    
WHERE     (mrLogItm.StatCdFrom <> 80) AND (mrLogItm.StatCdTo = 80)         
--1. AND (irEventDate.DteCreated >= @datefrom ) AND (irEventDate.DteCreated <= @dateto)   
--2. AND  irEventDate.DteCreated BETWEEN convert(Datetime,@datefrom,102) AND convert(Datetime,@dateto,102 )
--3. AND (irEventDate.DteCreated >= convert(nvarchar(20), @datefrom)) AND (irEventDate.DteCreated <= convert(nvarchar(20), @dateto))          
AND (mrCompany.NCRMrcNum IS NOT NULL)        
AND (irEventDate.TypeCd = 'FEESP')        
AND (a.TypeCd = 'CERTI')  
order By mrCompany.AO         
Posted
Updated 20-Mar-19 1:27am
Comments
Member 14114251 20-Mar-19 5:51am
   
Both my parameters are datetimes not NVARCHAR if you check well and also on the database the data type for this field is a datetime not NVARCHAR so am comparing strings.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Usually problems involving date time are related to improper handling of the data as text or the conversion of text to dates due to various formats of date throughout the world.

Your SP seems to be right as the parameters are of the proper type so we have one proper implementation. How about the table; Are the fields being searched DateTime as well?

What is calling this the Procedure? Are the parameters being passed in DateTime as well?

How are DateFrom/DateTo acquired? A date-picker or text entry? Is proper regionalization being applied in the conversion to DateTime?

Quote:
My challenge now is when am running this procedure passing values of this format '2019-02-31' is not pulling any record
This should not work, and should have thrown an error.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Simple: you are using the wrong format for data storage.
Because you pass dates as NVARCHAR, they are compared as strings - and the chances are you also store them as strings. String comparisons work on a character by character basis, which the whole result depending on the first pair of different characaters. Thjat's useless for dates!

Pass them in as DATETIME, DATETIME", or DATE instead (and pass DateTime values from your presentation language via parameters as well), change your DB to store them as DATE, DATETIME, or DATETIME2 and it should work.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100