Solved

Convert Date MM/DD/YYYY

Posted on 2006-06-08
18
2,112 Views
Last Modified: 2012-06-27
I would like to create three fields from one. I have a date field that displays 01/01/2006

I would like to create three formula fields that display characters:

MM (Characters 1 and 2)
DD (cHARACTERS 4 AND 5)
YYYY (Characters 7-10)  

I want to strop off the forward slashes.

0
Comment
Question by:jmorin1
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 16865749
you can access these directly using crystal syntax
month, day and year are all reserved terms.  for example,
global dateVar todaysDate := DataDate;
global numberVar todaysMonth := month(todaysDate);
global numberVar todaysDay := Day(todaysDate);
global numberVar todaysYear := Year(todaysDate);

todaysMonth will return just the numeric value of the month (6)
todaysDay will return the numeric value of the day (8)
and todaysYear will return the numeric value of the year (2006)
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 16865785
specific to your example, do:
dateVar testDate := date(2006,01,01);
month(testDate);      <--- this will return 1
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 25 total points
ID: 16865879
Looks good.  That works if you really have a date field.  If it is a text field and the data is stored in that format then use

Left({YourDateField},2) for the month
Mid({YourDateField},4,2) for the day
Right({YourDateField},4) for the year

mlmcc
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 

Author Comment

by:jmorin1
ID: 16865900
The field type is date.  I am retrieving birth dates from the system and would like to get what MLMCC showed however  I am a bit confused by zephyr's example.  

0
 

Author Comment

by:jmorin1
ID: 16865916
I am looking at Formulas and do not see the datevar function. Am I completely lost?
0
 

Author Comment

by:jmorin1
ID: 16865980
If I am retrieving a filed that is a date.  The value is 12/25/2006.

What would I do to create a formula field that strips the dashes and displays only the day of the month?  Then of course the year?
0
 

Author Comment

by:jmorin1
ID: 16866012
Month ({INDIVIDUAL.BIRTH_DATE})

If i use the above syntax it returns a one character value.  How can I get Crystal to display a leading zero for the months other than 10,11 or 12?
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 16866015
datevar is a type, similar to numbervar or stringvar.  if your field in the database is of type date, then you would declare the variable as follows:
datevar variablename := {database.field}

if your database field is of type string, then you would use mlmcc's suggestion

you do not need a formula to strip the dashes.  you can access these fields using built-in functions.  use my suggestion if the field is of type date, and use mlmcc's if the field is of type string.
0
 

Author Comment

by:jmorin1
ID: 16866042
zephyr_hex: I tried your method and it displays the value in the field as it normally would.  What syntax would I insert to return the year portion only?  Then the Day. Then the month?
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 16866058
if you want a leading zero, you could do something like:
if month({individual.birth_date}) < 10 then "0" + left(toText(month({individual.birth_date}),1);

this will cast the date to a string, add a leading zero and strip the .00 off
0
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 16866102
to return the year:
year({individual.birth_date);

to return the month, with leading zeros, see my last post... except it needs an else clause, so it should be:
if month({individual.birth_date}) < 10 then "0" + toText(month({individual.birth_date})) else month({individual.birth_date});

to return the day, with leading zeros, use the same structure:
if day({individual.birth_date}) < 10 then "0" + month(individual.birth_date}) else day({individual.birth_date});

if you are getting something like 06.00 as the result, then use the "left" to strip off the training .00's.  see the example in my previous post.
0
 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 75 total points
ID: 16866105
oops... i typo'd the year one
year({individual.birth_date});

forgot one of the curly brackets.  sorry
0
 

Author Comment

by:jmorin1
ID: 16866140
My brain is fried.  Are you suggesting I enter the syntax on your previous post for a field that posseses date properties?  It is not working.  

I was able to strip the month based on a built in function within Crystal. ie Month ({INDIVIDUAL.BIRTH_DATE}).  I am very new to Crystal so I must ask that you talk to me as if I was 6 years old.  The function I used returns a value of one character.  I need leading zeros.  Is there a way to get the leading zero entering syntax in the same formula or am I nuts?





0
 
LVL 28

Accepted Solution

by:
bdreed35 earned 400 total points
ID: 16866291
I haven't read the whole thread so forgive me if I overlooked something.

If you want three seperate fields to display, with leading zeros, the easy method is to use the totext function:

//@Year
totext({INDIVIDUAL.BIRTH_DATE},"yyyy")

//@Month
totext({INDIVIDUAL.BIRTH_DATE},"MM")

//@Day
totext({INDIVIDUAL.BIRTH_DATE},"dd")

Each formula returns a string, so that you can maintain the leading zero.
0
 

Author Comment

by:jmorin1
ID: 16866429
Thank you very much.  The totext solution was the way to go.  I appreciate zephyr's valiant effort as well.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16866686
Glad i could help

mlmcc
0
 

Author Comment

by:jmorin1
ID: 16870662
I appreciate your help as well mlmcc.  Sorry I left you out. Not my intention.  Have a great weekend to all!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16874518
You included me.  My first comment towards the top.

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

Suggested Solutions

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

829 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