| Author |
Message |
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
  
Topic: Converting string to date in formula
Posted: 08 Aug 2011 at 8:24am |
|
Hello,
I'm a newbie but understand basic report writing, its the formulas that trip me up. We have a table with dates in a field that is just string. So as I run this report I need to sort it by date but will need to convert that data. Can someone please help? I'm on 2008 v12.01 Thanks.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Aug 2011 at 9:04am |
|
what is the format of the string (sample data)?
|
| IP Logged |
|
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
Posted: 08 Aug 2011 at 9:40am |
|
Hello, this is a free form txt field and most, not all are like this, 08/01/2011
Thanks.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Aug 2011 at 10:02am |
since this is free form you need to acconut for invalid dates where users entered non dates. In this example it makes these non-date strings into 1-1-1900 date field
if isdate(table.field) then date(table.field) else date(1900,1,1)
|
| IP Logged |
|
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
Posted: 08 Aug 2011 at 10:10am |
|
It errors for me saying "results of the selection formula must be a boolean" ?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Aug 2011 at 10:27am |
I think you are putting this in the select expert.
The select expert is only used to evaluate rows for inclusion/exclusion in the report. It always wants a boolean result (true include / false exclude).
In the Field Explorer,
right click on Formula FIelds,
select New,
name it whatever you want and
add the formula there.
Save it and place it on the report canvas to see the results Edited by DBlank - 08 Aug 2011 at 10:29am
|
| IP Logged |
|
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
Posted: 08 Aug 2011 at 10:50am |
|
Brilliant! Thank you so much.
Afterwards it wasn't sorting by date correctly so I figured out to apply the new fx in the sort order vs. the field itself and it works.
|
| IP Logged |
|
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
Posted: 18 Aug 2011 at 11:36am |
|
Dear DBlank,
I have another related question to this string/date field if I may? How then would I setup some start-end date parameters to select a range of dates in the database before I've done the string-to-date conversion as you showed previously?
Would it help to create a view, then change the string field to a date, so we could more easily query? Thanks
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Aug 2011 at 11:40am |
Are you trying to create stored procedure params or crystal params?
|
| IP Logged |
|
Crystalmenow
Newbie
Joined: 08 Aug 2011
Online Status: Offline
Posts: 7
|
Posted: 19 Aug 2011 at 6:27am |
|
Crystal parms
|
| IP Logged |
|
|
|
