Solved

Convert Date MM/DD/YYYY

Posted on 2006-06-08
18
2,109 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 42

Expert Comment

by:zephyr_hex
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 42

Expert Comment

by:zephyr_hex
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
 

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 42

Expert Comment

by:zephyr_hex
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Expert Comment

by:zephyr_hex
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 42

Expert Comment

by:zephyr_hex
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 42

Assisted Solution

by:zephyr_hex
zephyr_hex 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

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

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. …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

10 Experts available now in Live!

Get 1:1 Help Now