Date format in SelectionFormula
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Writing Code
Forum Discription: .NET 2003 programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1137
Printed Date: 28 Apr 2024 at 9:18am
Topic: Date format in SelectionFormula
Posted By: wattsjr
Subject: Date format in SelectionFormula
Date Posted: 07 Aug 2007 at 9:51am
|
I want to use the DateTimePicker(s) to control the date ranges in my selection criteria, but I'm having trouble getting the right format.
The DateTimePicker.Text (or .Value) is giving me the date in "mm/dd/yyyy" format, but my "vCNDBReport.CrystalReportViewer1.SelectionFormula" seems to only retrieve records when I pass it a date in "yyyy,mm,dd" format.
What am I doing wrong?
My database is Oracle. I hope doesn't make a difference, but if it does, is there a reasonable way to get (convert to) the expected date format?
Any help would be truely appreciated.
Regards, ------------- -jrw |
Replies:
Posted By: BrianBischof
Date Posted: 07 Aug 2007 at 11:01am
|
Change the selection formula to use the CDate() function. This converts a string to the Date data type.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: wattsjr
Date Posted: 07 Aug 2007 at 3:27pm
|
Hi Brian,
I tried your suggestion in the code shown below and still ended up with an "mm/dd/yyyy" format in my selection string. Am I using it in the wrong place? I have several parameters to concatenate as part of the selection argument.
==============
If cboNamedDateRange.Text = "" Then
gFromDate = CDate(DateTimePicker1.Text)gToDate = CDate(DateTimePicker2.Text)gSelDateRange = "{SW_CASE.SWDATECREATED} in Date(" & gFromDate & ") to Date (" & gToDate & ") and " ElsegSelDateRange = "{SW_CASE.SWDATECREATED} in " & cboNamedDateRange.Text & " and " End IfSelect_String = gSelDateRange Select_String = Select_String & "{BRX_CONTACT.BRXSECURITY} = 'US' and {SW_CUSTOMER.SWREGION} = '120 '" vCNDBReport.CrystalReportViewer1.SelectionFormula = Select_String =======
Thanks and Regards, ------------- -jrw |
Posted By: BrianBischof
Date Posted: 07 Aug 2007 at 3:38pm
|
Why aren't you using Date() for the cboName... dropdown?. Plus, I think you need to put quotes around the data string within the actual selection formula. You have to act like you were typing the data formula into CR manually. Thus, if you would put quotes around a date string when typing it in then you would do the same when building the selection string with code.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: wattsjr
Date Posted: 08 Aug 2007 at 9:14am
|
The cboNamedDateRange contains a "Collection" of Crystal Date Ranges and is used exclusive of the FromDate and ToDate parameters. The values are:
LastFullMonth
LastFullWeek
MonthToDate
YearToDate
LastYearYTD
Calendar1stQtr
Calendar2ndQtr... etc.
and is used in the selection formula as shown here:
gSelDateRange = "{SW_CASE.SWDATECREATED} in YearToDate and "... plus Security and Region Parms
------
When I was initially testing the Report I used a hard-coded selection string:
gSelDateRange = "{SW_CASE.SWDATECREATED} in Date(2006,01,01) to Date (2007,08,07) and "... plus Security and Region Parms
and it worked as coded.
My final solution was to change the Definition of the global date fields (gFromDate, gToDate) from type "Date" to "String" and use the following to create the desired date format:
gFromDate = Format(DateTimePicker1.Value, "yyyy,MM,dd")
gToDate = Format(DateTimePicker2.Value, "yyyy,MM,dd")
Since the two options are mutually exclusive (controlled by Radial buttons) the user is kept from entering in bad dates. They either selecta Named Date Range from the dropdown list or the DateTimePicker prohibits bad date entries. And having to edit for bad dates is kept to a minimum. ------------- -jrw |
Posted By: mcaavijit
Date Posted: 09 Nov 2007 at 12:40pm
|
use the following code DateTimePicker1.Format = DateTimePickerFormat.Custom DateTimePicker1.CustomFormat = "yyyy,mm,dd" now u can use the DateTimePicker1.value thats it |
Posted By: shabbi
Date Posted: 21 Oct 2008 at 12:23am
|
hi all,
am facing the problem in crystal report design i selected date time from special fields but the problem is when i view second , third...... n th page the time is not constant seconds are changing i want it to be constant ie report generated time. Please help. thanks, Shabarish.V |
Posted By: GlennC
Date Posted: 24 Oct 2008 at 8:10am
|
WattsJr,
Just recently encountered and overcome a similar problem.
Here's the heart of the problem: In older reports, there was an option to "Convert Dates To String". (Under Report Options in the designer). For some reports I don't see that option, so I think it's probably not available in new version due to the problem it causes.
If the report has it set to convert dates to string, then you have to put in a string representation of a date in the selection formula.
If the report has it set to convert to date, or not set at all, then you have to use the crystal Date() function as BrianB suggested.
Now, this brings up another problem: After loading a report, how do you see this setting for that report?
In C++ you used to have access to this propery in the document object. I imaging you could in VB too. For the life of me, I can't find it in C#. I think they've retired it along with the setting itself in the designer. This was a huge problem for me.
I found a work around. You have to interrogate the type of the date field as such:
if (crDoc.Database.Tables["yourTable"].Fields["yourdateField"].ValueType == CrystalDecisions.Shared.FieldValueType.StringField)
{ ... use the string format ...}
else
{ ... use the date format ...}
What a pain this all was.
Shabbi, have you tried getting the datetime on an earlier pass, such as WhileReadingRecords or BeforeReadingRecords? Just a suggestion.
|
Posted By: shabbi
Date Posted: 24 Oct 2008 at 8:49am
|
thanks WattsJr for the reply,
actually am using JRCHelper.java fileand CrystalReport.jsp in my application to generate crystal reports  i haven't tried using whilereadingrecords or BeforeReadingRecords,can u please share what is
whilereadingrecords ,BeforeReadingRecords all about? i think we can convert date to string using ToText() method in section formula(i have not tried)so if i convert Date to String will my problem be fixed? |
Posted By: GlennC
Date Posted: 24 Oct 2008 at 10:29am
|
Shabbi,
Your problem seems to be different from WattsJR's. It should probably be it's own thread. But anyway,
I'm not too familiar with generating a report with java in this way. There's special fields called PrintDate and PrintTime. Yes, you can wrap them in ToText (PrintDate) if you want them to appear in the footer.
For me, it prints the same time on all pages.
When a report is run, it goes through the report in multiple passes. I'm sure this is in the book.
BeforeReadingRecords, and WhileReadingRecords are times when the report engine is processing the report. They happen before WhilePrintingRecords.
See if it'll let you put "BeforeReadingRecords" in the formula on the first line above "ToText(PrintTime)".
|
Posted By: shabbi
Date Posted: 28 Oct 2008 at 3:18am
|
hello GlennC,
am trying to improve the performance of crystal reports, so am using selection fromula where am giving all "where" conditions in selection fromulas. ie recode selection formula.what is hapenning now is while designing the reports it takes time to display say some 25k data.
is there any way i can improve performance?
|
Posted By: shabbi
Date Posted: 28 Oct 2008 at 3:25am
|
hai, how do i use such as BeforeReadingRecord. suppose if i use use BeforeReadingRecords;
{COMMAND.START_DATE}=DateValue ({?FromDate}); i get an alert message "formula cannot be evaluated at time specified"
but if i use
WhileReadingRecords;
{COMMAND.START_DATE}=DateValue ({?FromDate}); i dont get any errors
|
Posted By: j_tush
Date Posted: 04 Nov 2009 at 11:07am
|
how to convert this string date to datevar ..please help me... this below formula is giving me Boolean error datevar year_a; stringvar montselect; stringvar firstday:="1"; stringvar createdate; createdate:= totext (monthselect + "/" + firstday + "/" + year_a); datevar Finaldate:= cDate (createdate); thanks for supporting me ... ------------- Tushar Jindle |
Posted By: Vertex
Date Posted: 08 Nov 2009 at 12:26pm
|
hello....tell me please how to get first day and last day of any month my code is here if {Order.CreateDateTime}=.........varible for user's selected month ........ then {Order.CreateDateTime}< First day of any month.........and ............last day of any month >{Order.CreateDateTime} ------------- Hi Peoples |
Posted By: Vertex
Date Posted: 09 Nov 2009 at 12:02pm
|
To select the any month from any year and also the first date of that year & last date of the same month ...What is code ?? ------------- Hi Peoples |
Posted By: Vertex
Date Posted: 25 Nov 2009 at 7:26am
|
how to count these records for Given Timing count ({Order.OrderID}) ...for today count ({Order.OrderID}) ...for this month count ({Order.OrderID}) ...for this year ------------- Hi Peoples |
Posted By: Brinda
Date Posted: 27 Nov 2011 at 9:34pm
|
It s Working For me thanks :)
------------- Thanks & Regards |