cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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,

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

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

Answers (3)

Answers (3)

Former Member
0 Kudos

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.

former_member183750
Active Contributor
0 Kudos

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

JWiseman
Active Contributor
0 Kudos

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