Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert Date MM/DD/YYYY

Posted on 2006-06-08
18
Medium Priority
?
2,118 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
[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
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 44

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 44

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 101

Assisted Solution

by:mlmcc
mlmcc earned 100 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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 

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 44

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 44

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 44

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 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 300 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 1600 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 101

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 101

Expert Comment

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

mlmcc
0

Featured Post

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.

Question has a verified solution.

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

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 …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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