Totext and Dates
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=22251
Printed Date: 28 Apr 2024 at 10:40am
Topic: Totext and Dates
Posted By: bremen
Subject: Totext and Dates
Date Posted: 27 Feb 2017 at 2:32am
I am working with two date time fields. My current formula is
if
isnull ({Table.Datefield1}) then
{Table.Datefield2}else
totext ({Table.Datefield1} , 'dd-MMM-yy')
{Table.Datefield1} and {Table.Datefield2} are Date/time fields and I would like them formatted as dd-MMM-yy.
I tired to change {Table.Datefield2} to text as well, but I get an error saying too many arguments.
|
Replies:
Posted By: DBlank
Date Posted: 27 Feb 2017 at 2:54am
a couple of ways to approach this but in any case the output of the formula has to always be the same data type
if isnull ({Table.Date1}) then
totext({Table.Date2},'dd-MM-yy') else
totext ({Table.Date1} , 'dd-MMM-yy')
Or make the output always a date field and then use formatting on that
if isnull ({Table.Date1}) then
{Table.Date2} else {Table.Date1}
Right click the resulting formula field on report canvas and select format field option
Use the "Date and Time" tab to customize it to your desired visual output
Option 2 is usually better if you need to still do grouping or sorting on the result as it keeps the data as a datetime field and you can use the sorts and groups that are unique to that data type.
|
Posted By: bremen
Date Posted: 27 Feb 2017 at 3:10am
I tried both of your formulas witch no luck. Apparently what I thought was a Date Time is actually a string.....
Table.Date1 = DateTime
Table.Date2 = String
|
Posted By: DBlank
Date Posted: 27 Feb 2017 at 3:12am
|
what is the format of the string and is it consistent?
|
Posted By: bremen
Date Posted: 27 Feb 2017 at 3:16am
01/24/2016.02:20:16
Yes it is consistent thorough out
|
Posted By: DBlank
Date Posted: 27 Feb 2017 at 3:19am
if isnull ({Table.Date1}) then
DATE({Table.Date2}) else {Table.Date1}
Use the format option on the resulting field
|
Posted By: bremen
Date Posted: 27 Feb 2017 at 4:08am
|
I Input the formula and I don't get and error when I check for errors or save and close, but when the formula editor window closes I get an error "Bad Date format string"
|
Posted By: DBlank
Date Posted: 27 Feb 2017 at 6:20am
Date2 has non datevalues in it.
How do you want to handle those?
|
Posted By: DBlank
Date Posted: 27 Feb 2017 at 10:44am
|
You can use an isdate() condition to exclude the bad formatted dates and prevent the error but you still would need to know how you want the errors handled.
|
|