Convert Number to Date format
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6385
Printed Date: 06 May 2024 at 6:20am
Topic: Convert Number to Date format
Posted By: flazaro28
Subject: Convert Number to Date format
Date Posted: 18 May 2009 at 7:05am
|
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,
19,780,401, 19,790,701, 19,800,701, 19,800,001, 19,801,001, 19,811,005 |
Replies:
Posted By: DBlank
Date Posted: 18 May 2009 at 7:20am
|
There may be a better solution but here is one:
date(left(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field},0,""),4 )+"," + mid(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,5,2 - ,0,""),5,2 ) +"," + right(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,2 - ,0,""),2 ))
|
Posted By: lockwelle
Date Posted: 18 May 2009 at 7:21am
|
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 |
Posted By: flazaro28
Date Posted: 18 May 2009 at 7:26am
|
Originally posted by DBlank
There may be a better solution but here is one: date(left(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field},0,""),4 )+"," + mid(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,5,2 - ,0,""),5,2 ) +"," + right(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,2 - ,0,""),2 )) DBlank....When I try to pull this into the report it prompts me for an error
"Bad date format String"
|
Posted By: flazaro28
Date Posted: 18 May 2009 at 7:31am
|
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
 |
Posted By: flazaro28
Date Posted: 18 May 2009 at 7:36am
|
Originally posted by DBlank
There may be a better solution but here is one: date(left(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field},0,""),4 )+"," + mid(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,5,2 - ,0,""),5,2 ) +"," + right(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,2 - ,0,""),2 )) Can I get the output in this format
MM/DD/YYYY..
Basically I am comparing this date to CurrentDate()
|
Posted By: DBlank
Date Posted: 18 May 2009 at 9:35am
|
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. |
Posted By: lockwelle
Date Posted: 19 May 2009 at 6:32am
|
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
|
Posted By: DBlank
Date Posted: 19 May 2009 at 7:53am
|
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({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field},0,""),4 ) +"," + right(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,2 - ,0,""),2 ) +"," + mid(totext({ mailto:%7b@19,730,101%7d,0,%29,4 - table.field} mailto:%7b@19,730,101%7d,0,%29,5,2 - ,0,""),5,2 )) |
Posted By: crystalsupport
Date Posted: 22 May 2009 at 5:15am
|
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 cdate(val(mid(MyDate,4,2)),val(mid(MyDate,2,2)),val(left(MyDate,1))) else if len(trim(MyDate)) = 6 then cdate(val(mid(MyDate,5,2)),val(mid(MyDate,3,2)),val(left(MyDate,2))) else cdate(0,0,0) ------------- Regards, Crystal Support Team Reporting House Inc. http://www.reportinghouse.com |
Posted By: Norton
Date Posted: 09 Nov 2010 at 6:14am
|
This formula works perfect with one exception for my situation. When used where there are nulls, it fails. I'm trying to convert a Number to date "20101108". The report refreshes, however once I try to page through it or export... it pulls up the Formula Editor and states "Bad date format string". date(left(totext({ORDMST.OMRDAT},0,""),4)+"," + mid(totext({ORDMST.OMRDAT},0,""),5,2) +"," + right(totext({ORDMST.OMRDAT},0,""),2)) I'm looking for some help on how to handle null (blank) |
Posted By: DBlank
Date Posted: 09 Nov 2010 at 6:17am
| what date do you want to display if it is null? |
Posted By: Norton
Date Posted: 09 Nov 2010 at 6:20am
|
Thanks for the quick reply, thanks. Simple answer [blank or 'na' ]would be fine. Current date would work as well. |
Posted By: DBlank
Date Posted: 09 Nov 2010 at 6:24am
|
if isnull({ORDMST.OMRDAT}) then date(1900,1,1) else
date(left(totext({ORDMST.OMRDAT},0,""),4)+"," + mid(totext({ORDMST.OMRDAT},0,""),5,2) +"," + right(totext({ORDMST.OMRDAT},0,""),2))
conditionally suppress the field as
currentfieldvalue = date(1900,1,1)
|
Posted By: Norton
Date Posted: 09 Nov 2010 at 6:42am
| returns "Bad date format string" error. |
Posted By: DBlank
Date Posted: 09 Nov 2010 at 7:15am
| what is your raw data type and a few sample rows? |
Posted By: DBlank
Date Posted: 09 Nov 2010 at 7:20am
|
I see it is numeric but you just want a string of 'YYYYMMDD'?
if isnull({ORDMST.OMRDAT}) or {ORDMST.OMRDAT}=0 then ""
else totext({ORDMST.OMRDAT},0,"")
|