Skip to Content
author's profile photo Former Member
Former Member

Crystal 2013 is casting a function with a date result as a string result, sometimes??

I have an SQL Server function to take a JDEdwards numeric date and convert it to an SQL Date field.

CREATE FUNCTION [dbo].[date_ccyyddd_to_mmddyyyy]

(

@JulianDate as Numeric(18,0)

)

RETURNS Date

AS

BEGIN

Declare @ResultDate as Date

Set @ResultDate = DATEADD(YEAR, @JulianDate / 1000 + 1899, Cast('01/01/0001' as Date))

Set @ResultDate = DATEADD(Day, @JulianDate % 1000 -1, @ResultDate)

RETURN @ResultDate

END

In many query based reports we have used the function. It works quite nicely for all but one user. For this one user Crystal is casting the date result as a string, it will then display as a yyyy-mm-dd format instead of his default mm/dd/yyyy short date format. It would generally be ok but since it is typed as a string if the user exports the results to excel it is not recognizing the column as a date either. The strange piece of the equation is that if the user saves the report to the enterprise server and I open it, go to edit the SQL command, do nothing, close the edit box, it gives me the unmapped fields wizard where I can fix the report. Thus when I open or generate a new report with this function it "knows" the result is a date but with a specific user it sees the result as a string.

Has anyone had a similar situation or can lead me in the correct direction to fix this? Unfortunately, this user is our power user, he writes more reports than anyone else. It appeared as a problem when we upgraded from Crystal Enterprise 2008 to 2013.

Current work around for user is to use a crystal CDate() function on the report side but I would like to get the correct solution.

Any assistance appreciated,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Jul 23, 2014 at 11:06 PM

    I would check to see which version of the SQL Server client this particular user is working with (you don't mention whether it's ODBC or OleDB...) Specifically, the connection needs to use either version 10 or 11 of the SQL Server Native Client. The SQL Server Native Client that comes installed on most computers is version 6, which is not the same thing, and none of the drivers with "Microsoft" in the name should be used.

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 24, 2014 at 02:43 PM

    To add to Dell's suggestion:

    If OLE DB then use:

    MS SQL 2005 - OLE DB Provider

    MS SQL 2008 - SQL Native 10

    MS SQL 2012 - SQL Native 11

    MS SQL 2013 - SQL Native 11

    If ODBC then use:

    MS SQL 2005 - SQL Native

    MS SQL 2008 - SQL Native 10

    MS SQL 2012 - SQL Native 11

    MS SQL 2013 - SQL Native 11

    - Ludek

    Senior Support Engineer AGS Product Support, Global Support Center Canada

    Follow us on Twitter

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 23, 2014 at 10:35 PM

    hi Anthony,

    I haven't seen anything like this myself, however if that particular user creates a new report using that function what is the result?

    also, if the user opens an existing and problematic report in the report designer and goes to the Database menu and then Verify Database, does it proceed with the steps to fix up the database or just say that the database is up to date?

    -jamie

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 24, 2014 at 03:08 PM

    SQL Driver was the correct solution. His machine had the standard SQL Server driver. Installing of SQL Native Client 11.0 fixed the issue.

    Thanks for the quick and accurate responses.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.