Print Page | Close Window

Convert DateTime in Dataset to Date

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19021
Printed Date: 20 Apr 2025 at 8:12am


Topic: Convert DateTime in Dataset to Date
Posted By: ravikas
Subject: Convert DateTime in Dataset to Date
Date Posted: 03 Feb 2013 at 8:30pm
Hello,

I read data for report from database to dataset. There is 'date fields' in database, and when i create report using these fields, they also became 'date fields'. Since i am using C#, there are not such datatype as 'Date', only 'DateTime' and when i pass my dataset all 'Date fields' in reports become from expected '2013-01-01' to '2013-01-01 00:00:00'. To change all DateTimes to strings is not a solution because these fields may be used in formulas, etc, and CR do not understands them. I have 500+ reports which are already made. Maybe it is possible to create custom datatype, which CR would understand.

thanks for any help ;)


Replies:
Posted By: CircleD
Date Posted: 04 Feb 2013 at 12:26am
I may be wrong but have you tried using a formula such as Datetime=Date in the Select Expert formula field.

Posted By: ravikas
Date Posted: 04 Feb 2013 at 12:47am
Originally posted by CircleD

I may be wrong but have you tried using a formula such as Datetime=Date in the Select Expert formula field.


I can edit reports and convert data in formulas, but i have 500+ reports, and to check them all requires a lot of time... So looking for a way to do that programically

Posted By: hilfy
Date Posted: 04 Feb 2013 at 5:53am

The other option is to set the format on your date fields so that the time never shows.  You should be able to set this as the default for new reports.  If you're using Crystal outside of Visual Studio, go to the File menu, select Options, go to the Fields tab, click on the "Date and Time" button, go to the "Date and Time" tab and set the default format.  If you're designing in Visual Studio, go to the CrystalReports menu, select Design, then Defaults and follow the instructions above.

 
However, this only works for reports going forward.  For your existing reports, you'll have to either manually go back and reformat the display for the individual date fields, or write a program that will go through all of your .rpt files and do that for you (I think it's possible to do this in code....)
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ravikas
Date Posted: 04 Feb 2013 at 7:55pm
Originally posted by hilfy

The other option is to set the format on your date fields so that the time never shows.  You should be able to set this as the default for new reports.  If you're using Crystal outside of Visual Studio, go to the File menu, select Options, go to the Fields tab, click on the "Date and Time" button, go to the "Date and Time" tab and set the default format.  If you're designing in Visual Studio, go to the CrystalReports menu, select Design, then Defaults and follow the instructions above.

 
However, this only works for reports going forward.  For your existing reports, you'll have to either manually go back and reformat the display for the individual date fields, or write a program that will go through all of your .rpt files and do that for you (I think it's possible to do this in code....)
 
-Dell


thanks for your time and help ;) i chosen to write application which will edit my reports, and will post solution after i finish

Posted By: ravikas
Date Posted: 07 Feb 2013 at 9:36pm
My solution:
Firstly when create report (in designer), i use connection to DB therefore my date fields in report are "Date", when i open that report with DataSet date fields become "DateTime", and first action which i take is save report:

ISCDReportClientDocument rcd = rpt.ReportClientDocument;
rcd.Save();
rcd.Close();
rcd.Open(name);

It will save report file, with all "Date" fields replaced with 'DateTime' fields, and i will not be able to preview report in ReportDesigner because report source is set to DataSet not DB anymore.

Then i simply change all DateTime fields formating to only show date:

CrystalDecisions.ReportAppServer.ReportDefModel.ReportObjects objects = rcd.ReportDefController.ReportObjectController.GetAllReportObjects();
foreach (ISCRReportObject obj in objects)
{
     if (obj.Kind == CrReportObjectKindEnum.crReportObjectKindField)
     {
          ISCRFieldObject oFieldObject = (ISCRFieldObject)obj;
          if (oFieldObject.FieldValueType == CrFieldValueTypeEnum.crFieldValueTypeDateTimeField)
          {
               ISCRReportObject NewReportObject = obj.Clone(true);
               ISCRFieldObject oField = (ISCRFieldObject)NewReportObject;
               oField.FieldFormat.DateTimeFormat.DateTimeOrder = CrDateTimeOrderEnum.crDateTimeOrderDateOnly;
               rcd.ReportDefController.ReportObjectController.Modify(obj, NewReportObject);
          }
     }
}

Then i do same to all report subreports.

Posted By: hilfy
Date Posted: 08 Feb 2013 at 3:09am
Cool solution!  I'm glad you found it and posted the code!
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window