on 07-23-2014 11:22 PM
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.