converting string to date

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: swapna123 — 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

Posted By: DBlank — 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 — 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} >= {@ffStartDate}) and
(not hasvalue({?endDate}) OR {Command.TRANSDT} <= {@ffEndDate})

it throws me error that string is required here..as the transdt column is sting type.

Thanks

Swathi

Posted By: DBlank — 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})

(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)

date({Command.TRANSDT}) can be used to convert your string

Posted By: swapna123 — 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 — 14 Sep 2010 at 6:02am

This goes in the select expert:

(hasvalue({?endDate}) and date({Command.TRANSDT}) in {?startdate} to {?enddate})

((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 — 14 Sep 2010 at 6:21am

It says that "a date range is required here"

Posted By: swapna123 — 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 — 14 Sep 2010 at 6:29am

Do you have the enddate set to allow for NULLs in its properties?

Posted By: swapna123 — 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 — 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 — 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}) <={@ffenddate}to currentdate)

can you please look at this..

Posted By: DBlank — 14 Sep 2010 at 7:32am

(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {@ffenddate} to currentdate)

Edited by DBlank - 14 Sep 2010 at 7:33am

Posted By: DBlank — 14 Sep 2010 at 7:34am

Sorry that is not right either...

(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)

Posted By: swapna123 — 14 Sep 2010 at 8:49am

Hi..Could you get any solution for that problem?

Posted By: DBlank — 14 Sep 2010 at 8:52am

the last post did not work?...

(NOT (hasvalue({?endDate})) and date({Command.TRANSDT}) in {?startdate} to currentdate)

Posted By: swapna123 — 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 — 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 {?startdate} to currentdate)

and

({?location_param}= table.location)

Posted By: swapna123 — 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 — 14 Sep 2010 at 9:18am

AND

(not (HasValue({?Storeroom})) OR {Command.STOREROOM} = {?Storeroom})

Edited by DBlank - 14 Sep 2010 at 9:19am

Posted By: swapna123 — 14 Sep 2010 at 9:24am

Sorry..This is not working.still displays all the locations..

Posted By: DBlank — 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})

Edited by DBlank - 14 Sep 2010 at 9:27am

Posted By: swapna123 — 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 — 14 Sep 2010 at 10:03am

can you repost the full select statement with all parts.

Edited by DBlank - 15 Sep 2010 at 3:54am

Posted By: swapna123 — 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 — 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 — 15 Sep 2010 at 3:52am

Hi..Thanks You soo much..it works..