convert string to a date format....
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=8962
Printed Date: 04 May 2024 at 8:39am
Topic: convert string to a date format....
Posted By: Seabass
Subject: convert string to a date format....
Date 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
|
Replies:
Posted By: davejwhite
Date 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.
|
Posted By: Seabass
Date 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) |
Posted By: Seabass
Date 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
|
Posted By: davejwhite
Date 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. |
Posted By: Seabass
Date 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 ????
|
Posted By: davejwhite
Date 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. |
Posted By: Seabass
Date 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
|
Posted By: DBlank
Date 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)))
|
Posted By: Seabass
Date 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
|
Posted By: jbattelle
Date Posted: 14 Jan 2013 at 11:45am
| Hey I know its 3 years later, but I saw your post, and I am having the same error when I paste it. What did you change to fix it? Thanks |
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 3:11am
|
Hi Seabass,
What did you do to fix the syntax error? I can't figure it out.
Thanks for any help.
Jen
|
Posted By: DBlank
Date Posted: 15 Jan 2014 at 4:15am
|
NumberVar YearPart; YearPart := tonumber({data_date_field} [1 To 4]); |
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 4:22am
|
Thank you for the quick response.
I get an error of The string is non-numeric on the YearPart:= tonumber
|
Posted By: DBlank
Date Posted: 15 Jan 2014 at 4:25am
|
is the field you are converting from a string or a numeric field type? |
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 4:48am
|
It's actually showing as a memo fiield.
Example of what my view is:
1/2/2014 called and prescriber is Brock called office to see if they will RX for pt and phone line is busy, will try again.. Leann Williams LPN
> From: Schall > To: Williams, Leann M > From: Williams, Leann M > Sent: 1/2/2014 9:08 AM > From: Schall > To: Team, Orange > From: Williams, Leann M > Sent: 12/30/2013 4:19 PM |
Posted By: DBlank
Date Posted: 15 Jan 2014 at 5:01am
|
what do you want to do with the memo field?
I assume do something with one of the date strings imbedded in it but this had a lot of dates in it and they are strewn through out it.
|
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 5:05am
|
I need the minimum date. I know there are a lot of dates.
What I'm experiencing right now is when I tell the system to only show the last 5 days, it is searching the memo field and pulling in that record even if the message wasn't created in the last 5 days. The message could have been created 2 weeks ago but had a response in the last 5 days.
I've tried creating a minimum but I get the same results.
Thanks so much for any guidance.
 |
Posted By: DBlank
Date Posted: 15 Jan 2014 at 5:19am
|
this appears to be a string(memo) that is appended in descending order from newest to oldest with a standard format of:
> From: lname, fname mi > Sent: MM/dd/yyyy h:mm PM You would like to see anything that that has the most recent Sent date in the last 5 days.
Is that accurate?
If not please explain what is inaccurate and what you would chnage to make the statement accurate. |
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 5:23am
|
I need to see oldest date or date of creation. When the message originated. If the origination date doesn't fall in the 5 day window then I don't want to see it.
Sorry for the confusion.
|
Posted By: DBlank
Date Posted: 15 Jan 2014 at 5:26am
|
so I understand, that means if i were reading the memo field it would be the very last time that the string of
> Sent: MM/dd/yyyy h:mm PM
appears in the text.
That is the date you want to use as a filter...correct?
|
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 5:30am
| That is correct! |
Posted By: DBlank
Date Posted: 15 Jan 2014 at 5:58am
|
This will be slow.... but should work
stringvar t1;
stringvar t2; datevar d1; WhileReadingRecords; t1 := mid({table.memofield},instrrev({table.memofield},"> Sent: ")+8);
t2 := left(t1,instr(t1,"M")); d1 := date(t2); d1 |
Posted By: jwilliams
Date Posted: 15 Jan 2014 at 6:01am
|
Thank you. I'm gettin an error
Bad Date format string on d1 := date(t2);
|
Posted By: DBlank
Date Posted: 15 Jan 2014 at 6:09am
|
you will have to run each part of this seperately to see what is going on and debug it.
I assumed that you have a format on the process that inserts the data into the memo field so I went with your sample memo field.
create a formula field as
stringvar t1;
WhileReadingRecords; t1 := mid({table.memofield},instrrev({table.memofield},"> Sent: ")+8);
t1 that should return a string that starts with the date field you wanted to extract (but also includes the whole notation).
If that is correct move to the next part and see what t2 returns. It should trim off the everything right of the AM or PM on the date field. |
Posted By: blowinup
Date Posted: 09 Apr 2014 at 8:49am
|
Originally posted by Seabass
dave... look like i got it... i change a few bracket and add spaces and it work as intended !!!! Thanks It would have been nice if you had shared the edits you made to get it to work. |