Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Convert number to date  and date to number

Posted on 2004-03-20
Medium Priority
Last Modified: 2007-12-19
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.
Question by:jheg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 101

Accepted Solution

mlmcc earned 375 total points
ID: 10642750
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



Author Comment

ID: 10645022
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
LVL 101

Expert Comment

ID: 10645093
Try just

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.


Expert Comment

ID: 10647509
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)

Author Comment

ID: 10692393

I am going to award you the points but the correct statement is
The m should be Capital instead of lower case.

Thank You for your help
LVL 101

Expert Comment

ID: 10693630
Thanks.  I didn't have the references here or Crystal to try it on.


Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question