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


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
    Posts
    29

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

    Hello,
    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
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    How are the dates/time stored in the database?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

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

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Then loose the # signs...you only need those for Access dates.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Mar 2004
    Posts
    29

    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 & """)"

    instead.

    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, 07: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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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