| Author |
Message |
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
   Topic: data type is incorrect
Posted: 25 Jun 2013 at 11:17am |
|
verifying the DB does not update the datatype from string to datetime.
i updated the current datasource using both xml connection and oledb connection but the datatype for two of my fields does not change from string[22] to datetime.
if i create a new report i can use either datasources (xml or oledb) and the correct datatype gets displayed.
The problem is that i cannot start from a new report. Any idea?
Thanks
rick
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 26 Jun 2013 at 8:36am |
What are you connecting to? Tables or a stored proc? What version of Crystal are you using? -Dell
Edited by hilfy - 26 Jun 2013 at 8:36am
|
|
|
|
IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 26 Jun 2013 at 11:52am |
|
fields were mapped to xsd file. i switched to stored proc but that did not help.
it's a very old report, developed using CR6 and then converted thru the years to CR9, VS2008, VS2010, VS2012.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 27 Jun 2013 at 3:37am |
Is there something in particular you're trying to do with the fields that aren't changing to the correct type? -Dell
|
|
|
|
IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 27 Jun 2013 at 1:13pm |
|
There's an old function that i want to remove. DTSToDate is no longer supported and since the field comes in as dateTime i'd like to take full advantage of that. I'd like to be able to control date and time (including 12/24hr based on customer's settings) without hardcoding "mm/dd/yy" etc.
ToText(DTSToDate({XX_XXXX_RPT;1.EnteredDateTime}), 'MM/dd/yyyy')
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 28 Jun 2013 at 3:19am |
What format is the date in the string using? Which version of Crystal are you using? Have you looked at using either DateTime() instead of DTSToDate()? -Dell
Edited by hilfy - 28 Jun 2013 at 3:22am
|
|
|
|
IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 28 Jun 2013 at 6:20am |
|
string comes as '06/28/2013 23:00:01.999'. if I just use DateTime() in the formula below I get the error: "A string is required here)
if I wrap DateTime() into ToText(), I get error: Bad date-time format string
if {@stringName} = '' then
''
else
ToText(DateTime({xx_xxxxx_RPT;1.EnteredDateTime}))
However, my main problem is not the function itself. i'd like to understand why the data type in the dataset is not reflecting datetime. i believe i mentioned that if i create a brand new report and use the same xsd file (or stored proc) as datasource, the data type comes in correctly.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 28 Jun 2013 at 8:43am |
Have you tried Set Location? For no reason I've ever been able to determine, sometimes it works for stored procedures and sometimes it doesn't. Steps: 1. Go to Database menu and select "Set Location". 2. In the top pane, select the connection AND the stored procedure for the report. 3. In the bottom pane, create a new connection (even though it will point to the same place as the old connection...) and select the stored procedure. 4. Click on the Update button. Also, DateTime() is not working because of the ".999" at the end of the string. It expects whole numbers for Hours, Minutes, and Seconds. -Dell
|
|
|
|
IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 28 Jun 2013 at 2:34pm |
|
I tried the steps from 1 to 4 multiple times...it never picked the change in the two datetime fields. It looked like the value was cached somewhere. i also checked/unchecked "save data" but nothing worked.
as far as the DateTime() function, i'm aware that the problem was caused by the milliseconds but i cannot change that in the stored procedure. also, i prefer not to convert to string because i have users in different countries and i want them to be able to see the date and time based on their local settings rather than hardcoded to "US" or "Europe".
i really appreciate all your help. It's probably worth mentioning that this report has been revised 520+ times (based on the revision number). I'm pretty sure something corrupted the file. I was just hoping to figure out a way to reset the datatypes just in case it happens again on a report that would require hours of works if I have to re-write it from scratch.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 01 Jul 2013 at 3:15am |
One of the "tricks" I've used on the rare occasions when I've had to rewrite a report is to have both the old report and the new one open in Crystal at the same time - usually each in their own Crystal window instead of having them both in tabs. Aligning the windows makes it easy to make sure that the sections are the same height. You can then copy and paste the objects from a section in the old report to the corresponding section in the new report, keeping the object spacing and format. The only thing that I've had problems copying are lines - so I redraw those. If you have any formulas that are only used inside other formulas, you can drag the formula onto the old report then cut it and paste into the new report, delete the object on the new report and the formula will still be in the formula list in the new report. Then the only thing that you have to check are any section formulas that are used for things like suppression, new pages, etc. (Formulas on objects will come over with the copied objects.) -Dell
|
|
|
|
IP Logged |
|
|
|
