| Author |
Message |
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
   Topic: convert string to a date format....
Posted: 27 Jan 2010 at 5:42pm |
this should be an easy one, yet i just can't seem to figure it out.... 
In a report, the field i am using is a string with 8 characters.... look like "20091002".
I need to convert this into a date so i can then use it to summarize/group my record by month....
IK tried teh isdate, and it comes back and tell me a false response...
Any help will be greatly appreciated.
Sebastien
|
 IP Logged |
|
|
davejwhite
Newbie
Joined: 19 Jan 2010
Online Status: Offline
Posts: 34
|
Posted: 28 Jan 2010 at 1:19am |
Try this:
NumberVar YearPart;
NumberVar MonthPart;
NumberVar DayPart;
WhilePrintingRecords;
YearPart := {DateDataField}[1To4];
MonthPart := {DateDataField}[5To6];
DayPart := {DateDataField}[7To8];
Date (YearPart,MonthPart,DayPart)
You are using Field Subscripts (in the square brackets) to extract the part of the field relevant to the Date part, then making them into a real date by using the Date function. Of course, you don't have to use variables - that just makes it easier to read.
|
|
IP Logged |
|
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
Posted: 28 Jan 2010 at 4:50am |
Dave, Thanks for the quick reply....
Here is what i got...
I copied the following int he Format Field / Display string formula field.... and i get a message that it's missing the ] and it highlight the "to4]"
NumberVar YearPart;
NumberVar MonthPart;
NumberVar DayPart;
WhilePrintingRecords;
YearPart := {ESSR.BLDAT}[1To4];
MonthPart := {ESSR.BLDAT}[5To6];
DayPart := {ESSR.BLDAT}[7To8];
Date (YearPart,MonthPart,DayPart)
|
|
IP Logged |
|
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
Posted: 28 Jan 2010 at 6:00am |
dave... look like i got it... i change a few bracket and add spaces and it work as intended !!!!
Thanks
|
|
IP Logged |
|
davejwhite
Newbie
Joined: 19 Jan 2010
Online Status: Offline
Posts: 34
|
Posted: 28 Jan 2010 at 8:18am |
|
Well done. Sorry if there were syntax errors. That was off the top of my head - I hadn't tested it.
|
|
IP Logged |
|
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
Posted: 28 Jan 2010 at 8:25am |
no prob... thanks a lot...
trying to use that date to group on my report... can we use a formulas to group details ????
|
|
IP Logged |
|
davejwhite
Newbie
Joined: 19 Jan 2010
Online Status: Offline
Posts: 34
|
Posted: 29 Jan 2010 at 1:19am |
|
If you can see the formula in the list in the Group By dialog, you can use it. If the formula doesn't appear, it's because it is being calculated in the report process after CR does the grouping.
|
|
IP Logged |
|
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
Posted: 23 Feb 2010 at 6:01am |
Reviving an old one here...
So i was looking at the grouping, now the team met and they want to filter that date field for the records that the date is less than today... basically whatever is late....
The problem, is that the formula field does not show up in the select expert as a field i can use. I do have other formulas that do show up but not that one.... Is it something like the grouping, where the field formulas is being calculated in the report after the selection is made ??? if yes, how can i make this formulas field available to be use in the select expert ????
Thanks in advance
Sebastien
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Feb 2010 at 7:02am |
This is not available for grouping or record selection because you are using whileprintingrecords.
if you change the formula to use something like this you should be able to group or filter on it (as a date field):
date(tonumber(left({table.datestring},4)),tonumber(mid({table.datestring},5,2)),tonumber(right({table.datestring},2)))
Edited by DBlank - 23 Feb 2010 at 7:02am
|
|
IP Logged |
|
Seabass
Newbie
Joined: 06 Jun 2009
Location: United States
Online Status: Offline
Posts: 32
|
Posted: 23 Feb 2010 at 7:13am |
Awesome..... plug it in and it works like a charm !!!!
Thank you very much, i truly appreciate your quick help on this one !!!
Sebastien
|
|
IP Logged |
|
|
|
