Solved

Convert number to date  and date to number

Posted on 2004-03-20
6
778 Views
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.
0
Comment
Question by:jheg
  • 3
  • 2
6 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 total points
Comment Utility
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
 

Author Comment

by:jheg
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try just
ToText({?EnteredDate},'yyyymmdd')

mlmcc
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Expert Comment

by:PATKIRSCH
Comment Utility
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
 

Author Comment

by:jheg
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Thanks.  I didn't have the references here or Crystal to try it on.

mlmcc
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
I do a File --> Open and Crystal just sits there 5 43
CRRuntime - Alerts 9 45
Pictures In Crystal 10 26
C# Crystal Reports 15 61
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now