change string to date field

[modglin]

I have a string that is 20021231. I need to convert this to a date field so that I can get information for specific days of a week. I know if I have a date field I can use the formula day({datefield}) to return if it is a Monday, Tuesday and etc.
I need to run a report on Mondays that will give me total sales for each Monday within the last month. Same report needs to be ran on Tues and so on. I was going to put in the date range and in select expert have a perimeter where I could say the day I need is Monday, Tuesday and etc.
My problem is the field is not truely a date field, therefore I cannot use the day({datefield}) formula.
If I create a formula indicating the first four digits would be year, next two month and last two date and put concenate that together as 12/31/2002 it still did not look at this as a date field. HELP!!!

 

[synapsevampire]

In Crystal you might use:

Kinda tired, but this works:

cdate(val(left("20021231",4)),val(mid("20021231",5,2)),val(mid("20021231",7,2)))

Replace the date in quotes with your field.

An alternative is to create a SQL Expression, which will bring in a real date type column from the database. This is dependent upon the database you use.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

Try this:

Dayofweek(NumberToDate(ToNumber({YourField})))

NumberToDate() is a custom function you can download from the crystal decisions website. It converts an 8 digit integer in a YYYYMMDD format to a real date. I use it all the time as this is how Macola stores its dates. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[chelseatech]

That format is close to a DTS so what i would use is
DTStoDate(Picture({datefield},"XXXX/XX/XX&quot)

One of the few places where the Picture function does something useful. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm