Solved

Convert number to date  and date to number

Posted on 2004-03-20
6
782 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
[X]
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
6 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 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
ToText({?EnteredDate},'yyyymmdd')

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


mlmcc
0
 

Author Comment

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

Expert Comment

by:mlmcc
ID: 10645093
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.

 
LVL 8

Expert Comment

by:PATKIRSCH
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)
0
 

Author Comment

by:jheg
ID: 10692393
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
ID: 10693630
Thanks.  I didn't have the references here or Crystal to try it on.

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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