• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2120
  • Last Modified:

Convert Date MM/DD/YYYY

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
jmorin1
Asked:
jmorin1
  • 8
  • 6
  • 3
  • +1
3 Solutions
 
zephyr_hex (Megan)DeveloperCommented:
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
 
zephyr_hex (Megan)DeveloperCommented:
specific to your example, do:
dateVar testDate := date(2006,01,01);
month(testDate);      <--- this will return 1
0
 
mlmccCommented:
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
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
jmorin1Author Commented:
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
 
jmorin1Author Commented:
I am looking at Formulas and do not see the datevar function. Am I completely lost?
0
 
jmorin1Author Commented:
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
 
jmorin1Author Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
jmorin1Author Commented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
zephyr_hex (Megan)DeveloperCommented:
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
 
zephyr_hex (Megan)DeveloperCommented:
oops... i typo'd the year one
year({individual.birth_date});

forgot one of the curly brackets.  sorry
0
 
jmorin1Author Commented:
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
 
bdreed35Commented:
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
 
jmorin1Author Commented:
Thank you very much.  The totext solution was the way to go.  I appreciate zephyr's valiant effort as well.  
0
 
mlmccCommented:
Glad i could help

mlmcc
0
 
jmorin1Author Commented:
I appreciate your help as well mlmcc.  Sorry I left you out. Not my intention.  Have a great weekend to all!
0
 
mlmccCommented:
You included me.  My first comment towards the top.

mlmcc
0

Featured Post

New feature and membership benefit!

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

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now