Topic: Convert Number to Date format
Oldest Post First
Newest Post First
Page
1
2
of 2 Next >>
Author
Message
flazaro28
Topic: Convert Number to Date format
I have a date Column in Number format which is shown below.. for example 19,730,101 imples 1973 year January -- Month and 01-Date..
How can I convert this to date format??
19,730,101,
DBlank
There may be a better solution but here is one:
date(left(totext({ table.field}, 0,""),4)+"," + mid(totext({ table.field} ,0,""),5,2 ) +"," + right(totext({ table.field} ,0,""),2 ))
lockwelle
local numbervar aDate:={table.field};
local numbervar aDay := aDate/10;
local numbervar aMonth;
local datevar outDate;
aDate := aDate/10;
aMonth:=aDate/10;
aDate :=aDate/10;
outDate:=dateserial(aDate, aMonth, aDay);
outdate can be shared or local, depends on what you need it for. shared is probably a better choice.
HTH
flazaro28
Originally posted by DBlank There may be a better solution but here is one:
date(left(totext({ table.field}, 0,""),4)+"," + mid(totext({ table.field} ,0,""),5,2 ) +"," + right(totext({ table.field} ,0,""),2 ))
DBlank....When I try to pull this into the report it prompts me for an error
"Bad date format String"
flazaro28
Originally posted by lockwelle local numbervar aDate:={table.field};
local numbervar aDay := aDate/10;
local numbervar aMonth;
local datevar outDate;
aDate := aDate/10;
aMonth:=aDate/10;
aDate :=aDate/10;
outDate:=dateserial(aDate, aMonth, aDay);
outdate can be shared or local, depends on what you need it for. shared is probably a better choice.
HTH
Lockwelle...your formula prompts me for an error
flazaro28
Originally posted by DBlank There may be a better solution but here is one:
date(left(totext({ table.field}, 0,""),4)+"," + mid(totext({ table.field} ,0,""),5,2 ) +"," + right(totext({ table.field} ,0,""),2 ))
Can I get the output in this format
MM/DD/YYYY..
Basically I am comparing this date to CurrentDate()
DBlank
Date function should automatically convert it to that format.
Is your number format YYYYMMDD or YYYYDDMM?
My formula is set for YYYYMMDD, if it is the other invert that last two parts of it.
lockwelle
depends on what the data looks like. is the field a date field or just a number...I thought it was just a number.
what I would do is create a few test formulas that will use the same logic as my original formula, but skip the part about assigning (it's causing an error) but have each formula display a different date part...one for year, one for month, one for day and compare against the field. This will allow you to see what is happening.
After a quick review, I am willing to bet that the issue is there are decimal values, which can probably be fixed by using cint(), or fix() or looking in help. But I would 'see' what the formula is doing first.
HTH
DBlank
Based on your other post I have a feeling that the number is set as YYYYDDMM. If so,
Change the code I gave you to flip the last to parts of the formula:
date(left(totext({ table.field}, 0,""),4) +"," + right(totext({ table.field} ,0,""),2 ) { table.field} ,0,""),5,2 ))
crystalsupport
Hi fiazaro28,
if you have not get your answer yet try with it
it may help you.
stringvar MyDate:=trim(totext({table.field},0,""));
// replace table.field above with yours //
if len(trim(MyDate)) = 5 then
Regards,
-- Select Forum --
Announcements
Talk with the Author
Self-Publishing
Job Postings
New feature request
Report Design
Data Connectivity
Technical Questions
Tips and Tricks
Crystal Xcelsius
Report Design
Data Connectivity
Writing Code
Tips and Tricks
Report Design
Data Connectivity
Writing Code
Tips and Tricks
You cannot post new topics in this forumcannot reply to topics in this forumcannot delete your posts in this forumcannot edit your posts in this forumcannot create polls in this forumcannot vote in polls in this forum
IP Logged
