Print Page | Close Window

Dates Year Error

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8547
Printed Date: 06 May 2024 at 2:42am


Topic: Dates Year Error
Posted By: NewJack
Subject: Dates Year Error
Date Posted: 03 Dec 2009 at 6:56am
I am trying to create the following formula:
 
       datediff ("ww", cdate(invoice_date), currentdate)
 
When I add it to my report I get an error:
 
      "Dates must be between year 1 and year 9999."
 
In the DB2 DB it has the invoice field as a int. (20090123).
 
How can I work around this to get the number of days between these to dates?  Do I have to convert the invoice date in Crystal?  If so how?
 
Apprciate the help.


Replies:
Posted By: DBlank
Date Posted: 03 Dec 2009 at 7:08am
There are a number of ways to convert the int to a date field, here is one way:
 
datediff('ww',date(tonumber(left(totext( mailto:%7b@date%7d,0%29,2 - {invoice_date},0),4)),tonumber(mid(totext({invoice_date},0),5,2)),tonumber(right(totext({invoice_date},0),2 ))),currentdate)

Posted By: NewJack
Date Posted: 03 Dec 2009 at 7:33am

Thanks for the help....

I still get the error, but after playing around with what you send I did the following input and and output:
 
        input:     left(totext(invoice date),10)
 
        output:  20,090,922
 
How do I remove the commas?

Posted By: DBlank
Date Posted: 03 Dec 2009 at 7:51am
left(totext(invoice date),10,'')

Posted By: DBlank
Date Posted: 03 Dec 2009 at 7:59am
If it was choking on a comma try...
datediff('ww',date(tonumber(left(totext( mailto:%7b@date%7d,0%29,2 - {invoice_date},0,''),4)),tonumber(mid(totext({invoice_date},0,''),5,2)),tonumber(right(totext({invoice_date},0,''),2 ))),currentdate)

Posted By: NewJack
Date Posted: 03 Dec 2009 at 8:07am
You Rock.
 
Thanks for your help.


Print Page | Close Window