Print Page | Close Window

converting string to date

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=11105
Printed Date: 27 Apr 2024 at 8:26pm


Topic: converting string to date
Posted By: swapna123
Subject: converting string to date
Date Posted: 14 Sep 2010 at 4:30am
Hi All,
 
I think the problem which iam posting is simple..but unable to figure out..
I have column which is string[300] ineed to convert it to date.how can i write a formula for that..and moreover at runtime when the user enters the date..he wanted to enter only the startdate and if he doesn't enter the enddate it should take it as sysdate.
 
Any help would be appreciated...
 
Thanks
Swapna


Replies:
Posted By: DBlank
Date Posted: 14 Sep 2010 at 4:57am
1. What does the date string look like?
2. How are your prams set up for an end date and do you have crystal 2008?

Posted By: swapna123
Date Posted: 14 Sep 2010 at 5:08am

Hi..

My date string is in this format '08/25/2010 08:51:30'

and yes iam using crystal 2008..i have created two parameters and took and took the type as date and created a formula for both the parameters
 
IF HasValue({?StartDate})
THEN {?StartDate}
ELSE CurrentDate
 
 
and in the same way for endate also..when i select expert
 
not hasvalue({?startdate})OR {{Command.TRANSDT} >= mailto:%7b@ffStartDate - {@ffStartDate }) and
(not hasvalue({?endDate}) OR {Command.TRANSDT} <= mailto:%7b@ffEndDate - {@ffEndDate })
 
it throws me error that string is required here..as the transdt column is sting type.
 
Thanks
Swathi
 
 

Posted By: DBlank
Date Posted: 14 Sep 2010 at 5:18am
not very familiar with the new 2008 hasvalue() as I am still on vXI but I think this is what you want.
(hasvalue({?endDate}) and date({Command.TRANSDT}) in {?startdate} to {?enddate})
or
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)
 
 
date({Command.TRANSDT}) can be used to convert your string

Posted By: swapna123
Date Posted: 14 Sep 2010 at 5:52am
Hi..I tried both of them..but it doesn't work..can you suggest me what should i do..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 6:02am

This goes in the select expert:

 
(hasvalue({?endDate}) and date({Command.TRANSDT}) in {?startdate} to {?enddate})
or
((NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)
 
When you say iot does not work what error are you getting?

Posted By: swapna123
Date Posted: 14 Sep 2010 at 6:21am
It says that "a date range is required here"

Posted By: swapna123
Date Posted: 14 Sep 2010 at 6:26am
The problem is that i don't to enter the enddate..it optional..if nothing is given..i t should take as sysdate automatically..but it is forcing me to enter enddate..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 6:29am
Do you have the enddate set to allow for NULLs in its properties?

Posted By: swapna123
Date Posted: 14 Sep 2010 at 6:34am
I dont find anything to set it to null in properties..do u mean to say in parameters?

Posted By: DBlank
Date Posted: 14 Sep 2010 at 6:36am

Again I do not have 2008 but I think under the param under the options you can set if it is required or not.

Posted By: swapna123
Date Posted: 14 Sep 2010 at 7:30am
Hi..when i select the startdate(08/01/2010) and leave enddate as balnk..it displays all the data for all the months..i think there is some problem with the selection..
 
(hasvalue({?StartDate}) and date({Command.TRANSDT}) >= {?StartDate})
and
(hasvalue({?endDate}) and date({Command.TRANSDT}) <= {?enddate})
or
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) < mailto:=%7b@ffenddate%7dto - ={@ffenddate}to currentdate)
 
 
can you please look at this..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 7:32am
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in mailto:%7b@ffenddate%7d%20to - {@ffenddate} to currentdate)

Posted By: DBlank
Date Posted: 14 Sep 2010 at 7:34am
Sorry that is not right either...
 
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in  mailto:%7b@ffenddate%7d%20to - {?startdate} to currentdate)

Posted By: swapna123
Date Posted: 14 Sep 2010 at 8:49am
Hi..Could you get any solution for that problem?

Posted By: DBlank
Date Posted: 14 Sep 2010 at 8:52am
the last post did not work?...
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in  mailto:%7b@ffenddate%7d%20to - {?startdate} to currentdate)
 

Posted By: swapna123
Date Posted: 14 Sep 2010 at 9:02am
when i use that last post..it gives the data for all the locations..eventhough if i select only one location..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 9:04am
you did not mention having another parameter of location.
Add that as another condition
 
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in  mailto:%7b@ffenddate%7d%20to - {?startdate} to currentdate)
and
({?location_param}= table.location)
 

Posted By: swapna123
Date Posted: 14 Sep 2010 at 9:12am
Sorry About that..i thought date was showing problem..so thought of discussing this first..but i have used the other parameter also..
 
(not HasValue({?Storeroom}) OR {Command.STOREROOM} = {?Storeroom})
 
 
and the code which you provided..but it still shows for all locations.

Posted By: DBlank
Date Posted: 14 Sep 2010 at 9:18am
AND
(not (HasValue({?Storeroom})) OR {Command.STOREROOM} = {?Storeroom})

Posted By: swapna123
Date Posted: 14 Sep 2010 at 9:24am
Sorry..This is not working.still displays all the locations..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 9:27am
I moved the first parenth after i first posted my reply, did you see that change?
Also if you just use this as the select without anything else (dates) does it work?
 
(not (HasValue({?Storeroom})) OR {Command.STOREROOM} = {?Storeroom})

Posted By: swapna123
Date Posted: 14 Sep 2010 at 9:35am
I have seen the change you made..i tried in all the ways..it works without dates and it works correctly if i enter the enddate..which the user doesn't want to enter..still i have the same problem..and thanks for replying me with soo much patience..

Posted By: DBlank
Date Posted: 14 Sep 2010 at 10:03am

can you repost the full select statement with all parts.

Posted By: swapna123
Date Posted: 14 Sep 2010 at 10:12am
I have used this code
 
not (HasValue({?Storeroom})) OR {Command.STOREROOM} = {?Storeroom}) and
(hasvalue({?EndDate}) and date({Command.TRANSDT}) in {?startdate} to {?EndDate})or
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)
 

Posted By: DBlank
Date Posted: 14 Sep 2010 at 10:24am
(not (HasValue({?Storeroom})) OR {Command.STOREROOM} = {?Storeroom})
and
(
(hasvalue({?EndDate}) and date({Command.TRANSDT}) in {?startdate} to {?EndDate})
or
(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)
)

Posted By: swapna123
Date Posted: 15 Sep 2010 at 3:52am
Hi..Thanks You soo much..it works..


Print Page | Close Window