Convert Date To Number

[dbinfoweb]

Hello. Is there any way to covert current date to number and put it in this format (yyyymmdd)? If so, how do I add or subtract number of dates from it after conversion?
Thanks in advance.
The reason I want to do this is because I have date field in such format (yyyymmdd) which is number type and I want to set a date range in previous month in Record Selection.
Thanks.

 

[HowardHammerman]

The best approach would be to convert the number to a date and then do your selection using Crystal's built in date functions. Create a new formula with the following expression:

stringvar dt := totext({table.numberfield});
date(dt[1 to 4],dt[5 to 6],dt[7 to 8])

Then you can create a parameter field (of date type, range values) and then in the select expert set the parameter field equal to your formula.

Or you can use a record selection like this (assume that your formula from above was called 'newdate')

month({@newdate}) = DateAdd ("m", -1, currentdate)

you may have to add more logic if you have more than one
year of data.
Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[dgillz]

A very easy way to do what Howard is suggesting is to use the NumbertoDate function to convert your database fields to a date. Macola and ACCPAC, amoing other packages, stores the dates as an 8 digit integer in the YYYYMMDD format.

NumbertoDate(YYYYMMDD) converts a 8 digit integer to a true date. Then you can use it as a data in record selection formulas, with various data functions, etc.

NumbertoDate() is a UFL and is on Crystal Decisions' website.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dbinfoweb]

Thanks for posting the reply. It really help to get the picture of how to approach the problem.
However, it looks like I still got error of "a number is required here" before the function "date" when I put in:
stringvar dt := totext({table.numberfield});
date(dt[1 to 4],dt[5 to 6],dt[7 to 8]).

and I did not see DateAdd in the function list.

I browsed the Crystal Decision, was not able to locate the NumberToDate()...

Any idea? Please advise.
Thanks.

 

[dgillz]

The dateadd function started in v8, so what versionof CR are you using? The dateadd function is downloadable here:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/UFLDateAdd.exe.asp?rdrcnt=1

The NumbertoDate function is located here:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufltdate.exe.asp

The error 'a number is required here' is happening because you are taking pieces of a text field - Dt, your string variable - and using it in a date() function that needs 3 numbers as arguments.

There is no need to use a variable at all, or the dateadd function, in the problem as you explained it. After you add the numbertodate function, write a record selection formula: numbertodate({numericfield}) in LastFullMonth

That will get you the last complete months' data. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office