DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: DD/MM/YYYY date format issue with RecordSelectionFormula/Crystal Reports.NET

  1. #1
    Join Date
    Mar 2004

    DD/MM/YYYY date format issue with RecordSelectionFormula/Crystal Reports.NET

    I am using Crystal Reports.NET (from VB.NET), in VS 2008.

    I am creating a selection statement programitically using the RecordSelectionFormula property of the report class.

    There seems to be some bug/problem when using a regional date format of DD/MM/YYYY (English - Australia or New Zealand in this case) in this selection formula.

    The code and selection statement works perfectly in MM/DD/YYYY format (English - United States), but not at all with the above mentioned format. I have been able to reproduce this problem easily by switching the regional date settings back and forth.
    I am getting the date from a DateTimePicker control. The code I am using is below:

    objReport.RecordSelectionFormula = _

    "{AlarmOccurrence.StartTime} >= #" & FormatDateTime(dteStartDate.Value, DateFormat.ShortDate) & " 12:00 AM# AND " & _
    "{AlarmOccurrence.StartTime} <= #" & FormatDateTime(dteEndDate.Value, DateFormat.ShortDate) & " 11:59 PM#"

    ' In the above I am selecting all alarm occurrences for a date range
    ' from beginning of start date (12:00 AM) to end of end date (11:59 PM)
    ' dteStart and End Date are retrieved from DateTimePicker

    Does anyone know of an existing bug/problem with Crystal Reports.NET or know of something else I might be doing wrong?
    Thanks much!!!

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    How are the dates/time stored in the database?

  3. #3
    Join Date
    Mar 2004
    This column is stored as a "datetime" datatype in a SQL Server Express 2005 DB.

  4. #4
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    Then loose the # only need those for Access dates.

  5. #5
    Join Date
    Mar 2004

    I found it....

    I found the problem.
    Thank you for the reply Hack, that wasn't quite it, but it led me down the correct path for finding it.

    Crystal Reports DOES need the "#" signs ONLY with date literals in selection statements. VB may not, except access like you say, but crystal reports does.
    HOWEVER, that was the problem. Date literals in selection statements in Crystal Reports are ALWAYS interpreted as US date formats, regardless of what regional settings the system is using.
    The key is to use Crystal Reports functions, DateTime functions embedded in the selection string INSTEAD of hard coding the date literals. The DateTime functions will interpret the regional date settings.....................

    I had to use a:

    "{AlarmOccurrence.StartTime} >= DateTime(""" & strStartDate.Trim & """) And {AlarmOccurrence.StartTime} <= DateTime(""" & strEndDate.Trim & """)"


    After digging...and digging....and digging... see the doc below that I found from Crystal:

    CDateTime ("8/6/1976 1:20 am")
    CDateTime ("10:20 am")

    However, there is one key difference between using date-time literals and the above usage of CDateTime. Date-time literals always use U.S. English date formats rather than settings from the locale of the particular computer on which Crystal Reports is running.
    Thus, the date-time literal examples above would work on all computers. On the other hand, on a French system, you could use constructions like:
    CDateTime ("22 aout 1997") 'Same as #Aug 22, 1997#

Similar Threads

  1. Converting UTC date format into SQL date field
    By irvinesbest in forum Database
    Replies: 2
    Last Post: 05-05-2011, 02:27 AM
  2. Replies: 3
    Last Post: 11-03-2008, 08:14 AM
  3. Replies: 3
    Last Post: 10-05-2006, 07:03 PM
  4. combinnig date with dd/mm/yyyy format
    By anshita in forum Java
    Replies: 1
    Last Post: 04-14-2006, 09:15 AM
  5. Replies: 2
    Last Post: 06-29-2001, 11:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center