Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

Convert number to date and date to number

I have a field (Lst_Cnt_Date) in my database that stores dates as 20040320, I will like to convert that field to display as 03/20/2004. I also set up a parameter to ask the user to pick  a date using the date value in the parameter setup.  It seems the date value from crystal is returned as 3/20/04, I will like to convert this date to the number value 20040320 so I can compared it to the Lst_Cnt_Date) and give me all records after the date entered.
0
jheg
Asked:
jheg
  • 3
  • 2
1 Solution
 
mlmccCommented:
What type is Lst_Cnt_Date?

You could create a formula to do the conversion for display.

Assuming it is a string

Formula - DispDate
mid({MyView.Lst_Cnt_Date},2,5) & "/" & Right({MyView.Lst_Cnt_Date},2) & "/" & Left({MyView.Lst_Cnt_Date},4)

If it is a number
stringvar strDate;
strDate := ToText({MyView.Lst_Cnt_Date})
mid(strDate ,2,5) & "/" & Right(strDate ,2) & "/" & Left(strDate ,4)

For comparing
Convert the entered date to match your date field format
ToText({?EnteredDate},'yyyymmdd')

or
ToNumber(ToText({?EnteredDate},'yyyymmdd'))


mlmcc
0
 
jhegAuthor Commented:
I tried the ToNumber(ToText({?EnteredDate},'yyyymmdd')) string
but when I run the report I get the message THE STRING IS NON-NUMERIC. The field Lst_Cnt_Date is a numeric field
0
 
mlmccCommented:
Try just
ToText({?EnteredDate},'yyyymmdd')

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PATKIRSCHCommented:
Rather than just display the number as a date, do you actually need it to be a date? if so, you probably want a formula something like:

if isdate(totext(Lst_Cnt_Date,0,"")) = true
//tests validity for date conversion
then DTSTToDate(totext(Lst_Cnt_Date,0,""))
// this will actually convert it so that you can now use it as a date

Now, you can actually use the Date Parameter you'd mentioned as a date for selection, etc... (an users will be able to pick a date fromt he drop down calender)
0
 
jhegAuthor Commented:
mlmcc

I am going to award you the points but the correct statement is
ToNumber(ToText({?EnteredDate},'yyyyMMdd'))
The m should be Capital instead of lower case.

Thank You for your help
0
 
mlmccCommented:
Thanks.  I didn't have the references here or Crystal to try it on.

mlmcc
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now