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.
jhegAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mlmccConnect With a Mentor Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
All Courses

From novice to tech pro — start learning today.