• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Crystal Reports Question

Hi,

I have a Date defined in my SQL Server 2005 stored procedure as DateTime. The field is coming over as String in the Crystal Reports! Why is this happening and how can I fix it so the date comes over as DateTime in the report.

Thanks
0
rustypoot
Asked:
rustypoot
  • 7
  • 5
  • 2
  • +1
1 Solution
 
peter57rCommented:
Well in CR you may be able to use CdateTime({table.field}) to convert to a datetime value.

Which version of CR are you using?
0
 
mlmccCommented:
Can you post the SP?

mlmcc
0
 
rustypootAuthor Commented:
It is the Admit Date andDischarge Date - they come out as String in Crystal Reports XI. Thanks


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[hsp_eMAR_Downtime]

as

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

CREATE TABLE #tmpClient  
(  
 ClientGUID   numeric(16,0) NULL,  
 VisitGUID   numeric(16,0),  
 ChartGUID   numeric(16,0) NULL,  
 ClientDisplayName varchar(50) NULL,  
 CurrentLocation  varchar(50) NULL,  
 CurrentUnit        varchar(50) NULL,
 ProviderDisplayName varchar(50) NULL,  
 VisitStatus   char(3) NULL,  
 VisitIDCode   char(20) NULL,  
 AdmitDtm   datetime,
 DischargeDtm datetime,
 TypeCode varchar(50) NULL,
 BirthYearNum  int,  
 BirthMonthNum  int,  
 BirthDayNum   int,  
 GenderCode   varchar(15) NULL,  
 IDCode    char(20) NULL  
)  

CREATE TABLE #tmpTasks  
(  
 ClientGUID     numeric(16,0) NULL,  
 ClientVisitGUID numeric(16,0) NULL,
 OrderGUID                  numeric(16,0),
 OrderID      char(9) NULL,  
 OrderName     varchar(125) NULL,  
 OrderSignificantDtm   datetime NULL,  
 OrderSummaryLine   text NULL,  
 OrderIsConditional   bit,  
 ConditionsText    varchar(255) NULL,  
 OrderRequestedBy   varchar(83) NULL,  
 OrderCatalogMasterItemGUID numeric(16,0) NULL,  
 OrderAdminInstructions  varchar(2000) NULL,  
 OrderStart datetime,
 OrderStop datetime,
 OrderTaskGUID numeric(16,0),
 CatalogItemTaskGUID   numeric(16,0) NULL,  
 ScheduleTypeCode   varchar(30) NULL,  
 TaskName     varchar(370) NULL,  
 TaskStartDtm    datetime NULL,  
 TaskStopDtm     datetime NULL,  
 TaskScheduleTypeSeqNum  int,  
 TaskReviewCategory   varchar(30) NULL,
 TaskReviewCategorySeqNum int,
 OrderHeading varchar(255) NULL,  
 TaskSeqNum                  int,  
 PrimaryParentOccurrenceGUID numeric(16,0) NULL  
)  

CREATE TABLE #ResultSet (
ClientGUID numeric(16,0),
VisitGUID numeric(16,0),
ChartGUID numeric(16,0),
ClientDisplayName varchar(50),
CurrentLocation varchar(50),
CurrentUnit varchar(50),
ProviderDisplayName varchar(50),
VisitStatus varchar(3),
VisitIDCode varchar(20),
AdmitDtm datetime,
DischargeDtm datetime,
TypeCode varchar(50),
BirthYearNum int,
BirthMonthNum int,
BirthDayNum int,
GenderCode varchar(15),
IDCode varchar(20),
ClientGUIDVar varchar(16),
OrderGUIDVar varchar(16),
OrderTaskGUIDVar varchar(16),
CodeNum int,
Code varchar(255),
Text varchar(463),
SvrtyLevelCode varchar(400),
AllergyTypeCode varchar(15),
AllergyDeclarationType int,
CodeGUID numeric(16,0),
OrderGUID numeric(16,0),
OrderID varchar(9),
OrderName varchar(125),
OrderSignificantDtm datetime,
OrderSummaryLine varchar(2000),
OrderIsConditional int,
ConditionsText varchar(255),
OrderRequestedBy varchar(83),
OrderCatalogMasterItemGUID numeric(16,0),
OrderTaskGUID numeric(16,0),
CatalogItemTaskGUID numeric(16,0),
ScheduleTypeCode varchar(30),
TaskName varchar(370),
TaskStartDtm datetime,
TaskStopDtm datetime,
TaskScheduleTypeSeqNum int,
TaskReviewCategory varchar(30),
TaskReviewCategorySeqNum int,
OrderAdminInstructions varchar(2000),
OrderHeading varchar(255),
TaskSeqNum int,
PrimaryParentOccurrenceGUID numeric(16,0),
Height decimal(6,1),
Weight decimal(6,1),
BSA decimal(6,2),
OrderStart datetime,
OrderStop datetime)


INSERT INTO #tmpClient  
SELECT  /* Get a list of all visit info */  
  ClientGUID = cavl.ClientGUID,  
  VisitGUID = cv.GUID,  
  ChartGUID = cavl.ChartGUID,  
  cavl.ClientDisplayName,  
  cavl.CurrentLocation,
  cavl.LocnGroupName,
  cavl.ProviderDisplayName,  
  cavl.VisitStatus,  
  Replace(cavl.VisitIDCode, '-', '') as VisitIDCode,
  cavl.AdmitDtm,
  cavl.DischargeDtm,
  cavl.TypeCode,
  cavl.BirthYearNum,  
  cavl.BirthMonthNum,  
  cavl.BirthDayNum,  
  cavl.GenderCode,  
  Replace(cavl.IDCode, '-', '') as IDCode
FROM CV3ActiveVisitList cavl WITH(NOLOCK)
JOIN CV3ClientVisit cv WITH(NOLOCK)
      ON cv.ClientGUID = cavl.ClientGUID
    and cv.ChartGUID = cavl.ChartGUID
    and cv.GUID = cavl.GUID
WHERE
      cavl.VisitStatus = 'ADM'
      and cavl.TypeCode in ('Inpatient', 'Emergency')

--select * from #tmpClient

INSERT INTO #tmpTasks  
SELECT  
 cv.ClientGUID,  
 cv.VisitGUID,
 o.GUID,
 o.IDCode,  
 o.Name,  
 o.SignificantDtm,  
 o.SummaryLine,  
 o.IsConditional,  
 o.ConditionsText,  
 RequestedBy =  
  CASE  
   WHEN o.CareProviderGUID IS NOT NULL THEN  
    (SELECT ISNULL(DisplayName,'') + ' (' + ISNULL(OccupationCode,'') + ')'  
    FROM CV3User  
    WHERE CV3User.GUID = o.CareProviderGUID)  
   ELSE NULL  
  END,  
 o.OrderCatalogMasterItemGUID,  
 AdminInstructions,
 o.RequestedDtm,
 o.StopDtm,
 ot.GUID,  
 ot.CatalogItemTaskGUID,  
 TaskScheduleType = ot.ScheduleTypeCode,  
  TaskName =  
  CASE  
   when ot.TaskName = '<OrderName + SummaryLine>' then o.Name  
   else ot.TaskName  
  END,  
 ot.StartDtm,  
 ot.StopDtm,  
  TaskScheduleTypeSeqNum = st.SeqNum,  
  TaskReviewCategory = trc.Code,
  TaskReviewCategorySeqNum = trc.SeqNum,
  OrderHeading = Convert(varchar(255), o.Name + isnull(' ' + o.SummaryLine,'')),  
ot.TaskSeqNum,                  
ot.PrimaryParentOccurrenceGUID  
FROM
      #tmpClient cv
JOIN CV3Order o WITH(NOLOCK)
      on o.ClientGUID = cv.ClientGUID
      and o.ChartGUID = cv.ChartGUID
    and o.ClientVisitGUID = cv.VisitGUID
      and o.ClientVisitGUID = cv.VisitGUID
JOIN SXAMMOrder mmo  WITH(NOLOCK) --limit to medication orders
      on o.GUID = mmo.OrderGUID
LEFT OUTER JOIN CV3MedicationExtension m  WITH(NOLOCK)
    ON (o.GUID = m.GUID)  
JOIN CV3OrderTask ot WITH(NOLOCK)
      on ot.ClientGUID = o.ClientGUID
      and ot.ChartGUID = o.ChartGUID
      and ot.OrderGUID = o.GUID
JOIN CV3TaskScheduleType st WITH(NOLOCK)  
    ON ( ot.ScheduleTypeCode = st.Code )  
JOIN CV3CatalogItemTask cit  WITH(NOLOCK)
    ON ( ot.CatalogItemTaskGUID = cit.GUID)  
JOIN  CV3TaskReviewCategory trc  WITH(NOLOCK)
    ON ( cit.TaskReviewCategoryGUID = trc.GUID )  
WHERE
Convert(Varchar, ot.StopDtm, 101) >= Convert(Varchar, GetDate(), 101)
--      oto.TaskStatusCode in ()
--      and trc.Code in ()

--select * from #tmpTasks

INSERT INTO #ResultSet
SELECT #tmpClient.*,  
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar,
 cast(t.OrderGUID as varchar(16)) as OrderGUIDVar,
 cast(t.OrderTaskGUID as varchar(16)) as OrderTaskGUIDVar,
 CodeNum = 4,     /* Task Occurrence Info */  
 Code = '',  
 Text = '',  
 SvrtyLevelCode = '',  
 TypeCode = '',  
 AllergyDeclarationType = (
      select isnull(min(type), 4)
      from CV3AllergyDeclaration WITH(NOLOCK)
      where #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID
--            and #tmpClient.VisitGUID = CV3AllergyDeclaration.ClientVisitGUID
      AND CV3AllergyDeclaration.Status = 'Active'),
 CodeGUID = 0,  
 t.OrderGUID,  
 t.OrderID,  
 t.OrderName,  
 t.OrderSignificantDtm,  
 t.OrderSummaryLine,  
 t.OrderIsConditional,  
 t.ConditionsText,  
 t.OrderRequestedBy,  
 t.OrderCatalogMasterItemGUID,  
 t.OrderTaskGUID,  
 t.CatalogItemTaskGUID,  
 t.ScheduleTypeCode,  
 t.TaskName,  
 t.TaskStartDtm,  
 t.TaskStopDtm,  
 t.TaskScheduleTypeSeqNum,  
 t.TaskReviewCategory,    
 t.TaskReviewCategorySeqNum,  
 t.OrderAdminInstructions,  
 OrderHeading,  
 t.TaskSeqNum,  
 t.PrimaryParentOccurrenceGUID,
Height = NULL,
Weight = NULL,
BSA = NULL,
  t.OrderStart,
  t.OrderStop
FROM #tmpClient  
 LEFT OUTER JOIN #tmpTasks t  
  ON ( #tmpClient.ClientGUID = t.ClientGUID  
   AND #tmpClient.VisitGUID = t.ClientVisitGUID )  
 
UNION ALL  
 
SELECT #tmpClient.*,  
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar,
 NULL as OrderGUIDVar,
 NULL as OrderTaskGUIDVar,
 CodeNum = 1,     /* Standard Allergy Info */  
 Code = CV3Allergen.Code,  
 Text = ISNULL(CV3Allergen.Code,'') + ' - ' + ISNULL(CV3AllergyDeclaration.SvrtyLevelDisplay,''),  
 CV3AllergyDeclaration.SvrtyLevelDisplay,  
 CV3Allergen.TypeCode,  
 AllergyDeclarationType = 0, --(select min(type) from CV3AllergyDeclaration where #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID AND CV3AllergyDeclaration.Status = 'Active'),
 CodeGUID = CV3AllergyDeclaration.GUID,  
 OrderGUID = NULL,  
 OrderID = NULL,  
 OrderName = NULL,  
 OrderSignificantDtm = NULL,  
 OrderSummaryLine = NULL,  
 OrderIsConditional = NULL,  
 ConditionsText = NULL,  
 OrderRequestedBy = NULL,  
 OrderCatalogMasterItemGUID = NULL,  
 TaskGUID = NULL,  
 CatalogItemTaskGUID = NULL,  
 TaskScheduleType = NULL,  
 TaskName = NULL,  
 TaskStartDtm = NULL,  
 TaskStopDtm = NULL,  
 TaskScheduleTypeSeqNum = NULL,  
 TaskReviewCategory = NULL,  
 TaskReviewCategorySeqNum = NULL,  
 OrderAdminInstructions = NULL,  
 OrderHeading = NULL,  
 TaskSeqNum = 0,  
 PrimaryParentOccurrenceGUID = NULL,
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient  
 JOIN CV3AllergyDeclaration  WITH(NOLOCK)
    JOIN CV3Allergen  WITH(NOLOCK)
    ON ( CV3Allergen.GUID = CV3AllergyDeclaration.AllergenGUID )  
  ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID  
   AND CV3AllergyDeclaration.Status = 'Active'  
   AND CV3AllergyDeclaration.Type = 0)  
 
UNION ALL  
 
SELECT #tmpClient.*,  
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar,
 NULL as OrderGUIDVar,
 NULL as OrderTaskGUIDVar,
 CodeNum = 1,     /* '<Other>' Allergy Info */  
 Code = CV3EnumReference.ReferenceString,  
 Text = Convert(varchar(50),CV3AllergyDeclaration.Text) + isnull(' - ' + CV3AllergyDeclaration.SvrtyLevelDisplay, ''),  
 CV3AllergyDeclaration.SvrtyLevelDisplay,  
 CV3AllergyDeclaration.TypeCodeForOther,  
 AllergyDeclarationType = CV3AllergyDeclaration.Type,
 CodeGUID = CV3AllergyDeclaration.GUID,  
 OrderGUID = NULL,  
 OrderID = NULL,  
 OrderName = NULL,  
 OrderSignificantDtm = NULL,  
 OrderSummaryLine = NULL,  
 OrderIsConditional = NULL,  
 ConditionsText = NULL,  
 OrderRequestedBy = NULL,  
 OrderCatalogMasterItemGUID = NULL,  
 TaskGUID = NULL,  
 CatalogItemTaskGUID = NULL,  
 TaskScheduleType = NULL,  
 TaskName = NULL,  
 TaskStartDtm = NULL,  
 TaskStopDtm = NULL,  
 TaskScheduleTypeSeqNum = NULL,  
 TaskReviewCategory = NULL,  
 TaskReviewCategorySeqNum = NULL,  
 OrderAdminInstructions = NULL,  
 OrderHeading = NULL,  
 TaskSeqNum = 0,  
 PrimaryParentOccurrenceGUID = NULL,
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient  
  JOIN CV3AllergyDeclaration  WITH(NOLOCK)
  JOIN CV3EnumReference  WITH(NOLOCK)
   ON ( CV3EnumReference.EnumValue = CV3AllergyDeclaration.Type  
    AND Cv3EnumReference.TableName = 'CV3AllergyDeclaration'  
    AND CV3EnumReference.Columnname = 'Type')  
  ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID  
   AND CV3AllergyDeclaration.Status = 'Active'  
   AND CV3AllergyDeclaration.Type = 1)  
 
UNION ALL  
 
SELECT #tmpClient.*,  
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar,
 NULL as OrderGUIDVar,
 NULL as OrderTaskGUIDVar,
 CodeNum = 1,     /* 'AllergyStatusUnknown' or 'UnknownAllergies' Allergy Info */  
 HVCEnvProfile.Value as Code,  
 NULL as Text,  
 NULL as SvrtyLevelCode,  
 NULL as TypeCode, /* put info on this value since if this value is empty no allergy prints */  
 AllergyDeclarationType = CV3AllergyDeclaration.Type,
 CodeGUID = CV3AllergyDeclaration.GUID,  
 OrderGUID = NULL,  
 OrderID = NULL,  
 OrderName = NULL,  
 OrderSignificantDtm = NULL,  
 OrderSummaryLine = NULL,  
 OrderIsConditional = NULL,  
 ConditionsText = NULL,  
 OrderRequestedBy = NULL,  
 OrderCatalogMasterItemGUID = NULL,  
 TaskGUID = NULL,  
 CatalogItemTaskGUID = NULL,  
 TaskScheduleType = NULL,  
 TaskName = NULL,  
 TaskStartDtm = NULL,  
 TaskStopDtm = NULL,  
 TaskScheduleTypeSeqNum = NULL,  
 TaskReviewCategory = NULL,  
 TaskReviewCategorySeqNum = NULL,  
 OrderAdminInstructions = NULL,  
 OrderHeading = NULL,  
 TaskSeqNum = 0,  
 PrimaryParentOccurrenceGUID = NULL,
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient  
  JOIN CV3AllergyDeclaration  WITH(NOLOCK)
  JOIN CV3EnumReference  WITH(NOLOCK)
    ON ( CV3EnumReference.EnumValue = CV3AllergyDeclaration.Type  
     AND Cv3EnumReference.TableName = 'CV3AllergyDeclaration'  
     AND CV3EnumReference.Columnname = 'Type')  
 JOIN HVCEnvProfile  WITH(NOLOCK)
             on( HVCEnvProfile.HierarchyCode = 'Client Info'  
              and HVCEnvProfile.Code = CV3EnumReference.ReferenceString)  
          ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID  
           AND CV3AllergyDeclaration.Status = 'Active'  
           AND CV3AllergyDeclaration.Type > 1)  

UNION ALL

SELECT #tmpClient.*,
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar,
 NULL as OrderGUIDVar,
 NULL as OrderTaskGUIDVar,
 CodeNum = 2,     /* Height/Weight information */  
 NULL,  
 NULL,  
 NULL,  
 NULL,
 AllergyDeclarationType = (
      select isnull(min(type), 4)
      from CV3AllergyDeclaration WITH(NOLOCK)
      where #tmpClient.ClientGUID = CV3AllergyDeclaration.ClientGUID
--            and #tmpClient.VisitGUID = CV3AllergyDeclaration.ClientVisitGUID
      AND CV3AllergyDeclaration.Status = 'Active'),
 CodeGUID = NULL,  
 OrderGUID = NULL,  
 OrderID = NULL,  
 OrderName = NULL,  
 OrderSignificantDtm = NULL,  
 OrderSummaryLine = NULL,  
 OrderIsConditional = NULL,  
 ConditionsText = NULL,  
 OrderRequestedBy = NULL,  
 OrderCatalogMasterItemGUID = NULL,  
 TaskGUID = NULL,  
 CatalogItemTaskGUID = NULL,  
 TaskScheduleType = NULL,  
 TaskName = NULL,  
 TaskStartDtm = NULL,  
 TaskStopDtm = NULL,  
 TaskScheduleTypeSeqNum = NULL,  
 TaskReviewCategory = NULL,  
 TaskReviewCategorySeqNum = NULL,  
 OrderAdminInstructions = NULL,  
 OrderHeading = NULL,  
 TaskSeqNum = 0,  
 PrimaryParentOccurrenceGUID = NULL,
 Height = Convert(decimal(6,1),HT.text),
 Weight = convert(decimal(6,1),convert(float,WT.text)/1000.0),
 BSA = CASE
             WHEN (Year(getdate())-#tmpClient.BirthYearNum) > = 18
            THEN Convert(decimal(6,2),(Power(Convert(decimal(6,2),HT.text),0.725) * Power(convert(decimal(6,2),convert(float,WT.text)/1000.0),0.425)) * 0.007184)
            ELSE Convert(decimal(6,2),SQRT((Convert(decimal(6,2),HT.text) * (convert(decimal(6,2),convert(float,WT.text)/3600)))))
             END,
OrderStart = NULL,
OrderStop = NULL

FROM #tmpClient  
LEFT OUTER JOIN CV3PhysicalNoteDeclaration HT WITH(NOLOCK)
            on #tmpClient.ClientGUID = HT.ClientGUID
                  --AND #tmpClient.ChartGUID = HT.ChartGUID  --UNCOMMENT WHEN MOVING TO LIVE
                  --AND #tmpClient.VisitGUID = HT.ClientVisitGUID --UNCOMMENT WHEN MOVING TO LIVE
                  AND HT.Status = 'Active'
                  AND HT.TypeCode = 'Height'
                  AND HT.Text is NOT NULL
                  AND HT.Text <> '0'
LEFT OUTER JOIN CV3PhysicalNoteDeclaration WT WITH(NOLOCK)
            on #tmpClient.ClientGUID = WT.ClientGUID
                  --AND #tmpClient.ChartGUID = WT.ChartGUID --UNCOMMENT WHEN MOVING TO LIVE
                  -- AND #tmpClient.VisitGUID = WT.ClientVisitGUID  --UNCOMMENT WHEN MOVING TO LIVE
                  AND WT.Status = 'Active'
                  AND WT.TypeCode = 'Weight'



-- Compensate when no allergy information has been added to visit
INSERT INTO #ResultSet
SELECT c.*,  
 cast(c.ClientGUID as varchar(16)) as ClientGUIDVar,
 NULL as OrderGUIDVar,
 NULL as OrderTaskGUIDVar,

 CodeNum = 1,     /* When no allergy info has been added to patient chart, return row stating this */  
 'Allergy Info Not Entered' as Code,  
 NULL as Text,  
 NULL as SvrtyLevelCode,  
 NULL as AllergyTypeCode,  

 AllergyDeclarationType = 4,
 CodeGUID = NULL,  
 OrderGUID = NULL,  
 OrderID = NULL,  
 OrderName = NULL,  

 OrderSignificantDtm = NULL,  
 OrderSummaryLine = NULL,  
 OrderIsConditional = NULL,  
 ConditionsText = NULL,  
 OrderRequestedBy = NULL,  

 OrderCatalogMasterItemGUID = NULL,  
 TaskGUID = NULL,  
 CatalogItemTaskGUID = NULL,  
 TaskScheduleType = NULL,  
 TaskName = NULL,  

 TaskStartDtm = NULL,  
 TaskStopDtm = NULL,  
 TaskScheduleTypeSeqNum = NULL,  
 TaskReviewCategory = NULL,  
 TaskReviewCategorySeqNum = NULL,  

 OrderAdminInstructions = NULL,  
 OrderHeading = NULL,  
 TaskSeqNum = 0,  
 PrimaryParentOccurrenceGUID = NULL,
Height = NULL,

Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
 
FROM #tmpClient c
where not exists (SELECT VisitGUID from #ResultSet rs where rs.VisitGUID = c.VisitGUID and rs.CodeNum = 1)


select *
FROM #ResultSet
order by
ClientDisplayName, VisitGUID, CodeNum, ScheduleTypeCode, TaskReviewCategory , TaskName




0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mlmccCommented:
I don't see where you are selecting the date fields in the final select statement.

mlmcc
0
 
rustypootAuthor Commented:
The dates are in:

INSERT INTO #ResultSet
SELECT #tmpClient.*,  
 cast(#tmpClient.ClientGUID as varchar(16)) as ClientGUIDVar

The dates are part of Client Temp table. The code above is ater INSERT INTO #tmpTasks .
0
 
mlmccCommented:
But how does Crystal see them.  It should only recognize the final result set you are selecting.

mlmcc
0
 
rustypootAuthor Commented:
Crystal sees it as String 30!  When I run the SP in SQL Server, it returns as DateTime!!!
0
 
mlmccCommented:
I know they are supposed to be temporary tables but have you made sure they are deleted after you run the procedure?

What types are te fields in CV3ActiveVisitList, since that is the source.

How are you connecting the report to the SP?
OLE, ODBC, other?

mlmcc
0
 
rustypootAuthor Commented:
The fields are defined as DateTime in tables; I am connecting via ODBC to Crystal.
0
 
mlmccCommented:
Can you try using OLE?

I have seen questions here that seem to indicate some ODBC drivers convert dates to strings.

mlmcc
0
 
rustypootAuthor Commented:
Thanks so much. I tried to use OLD but it did not work either!

So, I just created a brand new rpt and brought in the SP and it works just fine!!! Very odd....
0
 
mlmccCommented:
One thing we didn't try on the original report if you have it, VERIFY DATABASE
Since a new report gets it correctly, I wonder if there was a change to the tables and Cystal will only pick it up if you force it with a VERIFY DATABASE

mlmcc
0
 
James0628Commented:
mlmcc may have a point.  Were those columns originally string, instead of datetime?  If so, that could be the problem.  I haven't had data types change often, so I don't think I've run into that specific situation, but I had a column that was made longer at one point, and all of my reports that used that column showed the old length until I did a "verify database".  FWIW, almost all of my reports use stored procedures, so they were getting the column from a stored procedure, like your report (if that matters).

 James
0
 
mlmccCommented:
I was thinking more along the line that a field was added/deleted from the list of fields in the query and Crystal was using the position rather than the name of the field to determine which element to use.  At one Crystal stored the position of the field rather than the name of the field in the query (thus the need for VERIFY DATABASE).

I have heard they changed the method to use the name but I don't know when they did that.  CR8 used positions in the query list so even if you just changed the order it could cause problems.

mlmcc
0
 
James0628Commented:
Yeah, I guess that could be a possibility too.

 James
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now