Print Page | Close Window

Century part of date is incorrect

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20336
Printed Date: 03 May 2024 at 9:37am


Topic: Century part of date is incorrect
Posted By: ChzHdGrrl
Subject: Century part of date is incorrect
Date Posted: 10 Jan 2014 at 5:39am
Hi:
 
I'm working with a system whose backend is written in AcuCobol, which is as alien to me as Urdu. The report is thirteen years old and is really ugly, but I'm trying to solve one of the more pressing and annoying problems with it first.
 
I have a field called Maturity Date. The field has a datatype of "date", and when I browse the data I see a number of dates formatted as MMDDYYYY, all of which appear to be correct. However, several of those dates display in my report with the wrong century (i.e. 19 as opposed to 20).
 
For example, when I browse the data for this field I see a number of dates 12/31/2049, but in the report those same dates display as 12/31/1949.
 
I have tried converting the field to a DATETIME and then done a DATEADD to the year, but THEN I get 4/10/1950 (which means it's adding to the days, not years). As an experiment I tried adding 100 to both the days and months, and neither yields the correct century.
 
I'm out of ideas. Anyone?
 
Thanks,
Chzhdgrrl


Replies:
Posted By: kevlray
Date Posted: 10 Jan 2014 at 6:39am
I think that converting to a string and building it up to look like you need (example):
totext({datefield},"MM/DD/YYYY")    (I may not have the formatting right, might be something like "mm/dd/yyyy")

I hope this helps.

Posted By: ChzHdGrrl
Date Posted: 10 Jan 2014 at 6:50am
I tried converting to a string and ended up with dates that look like this:
 
05/DD/YYYY
10/DD/YYYY
04/DD/YYYY
 
WTH?

Posted By: ChzHdGrrl
Date Posted: 10 Jan 2014 at 7:02am
I was mistaken in my original post....the source data is also wrong. The dates as displayed in the user interface are correct, but when I browse the data in its native field (not the field resulting from the conversion), it's wrong.
 
Any suggestions?

Posted By: kevlray
Date Posted: 10 Jan 2014 at 7:37am
If the source data is incorrect, then it would require some 'smart' programming to figure out how to correct the dates.  Which probably should be done on the database side.  

As far as the totext function.   I did not look up to see the correct formats for the dates, I relied on my memory which is rather faulty.

Posted By: ChzHdGrrl
Date Posted: 10 Jan 2014 at 7:40am
Sadly, this is an OTS product and I don't speak AcuCobol, so any database side work will come with a pricetag. :(
 
Thanks anywho.

Posted By: kostya1122
Date Posted: 10 Jan 2014 at 10:29am
if there are not a lot of wrong dates you could just do an
if statement.
this might be a bad solution if the future dates might be also incorrect.

Posted By: DBlank
Date Posted: 10 Jan 2014 at 11:28am
just a suggestion but it might be storing the date as an integer of total number of days (or minutes or something) from a starting date (maybe datetime?).
if that is the case you can use the original data that is really an integer in a dateadd to whatever the startingdate is to get to the correct date in crystal.
If you can find a data field, see the raw field data in its native program and then see the same field as it is dispalyed as in the GUI that shows it correctly you might be able to determine it.


Print Page | Close Window