More Date Problems 1

[TheMagikWand]

hey guys/gals,

im working on CR v8.5 and came across this "date" problem. In my database dates are stored as a # field such that...
60,203.00 corresponds w/ 6/02/03.

i have a report to do today and i need to get my dates to look like the latter, but i cannot use the numbertodate(x) function because the dates arent stored as CCYYMMDD.

i made a long winded formula using the totext function to just grab 1 or 2 #'s at a time, but the formula is lengthy and i dont know if it will hold up for every date, anyone have any suggestions???

Thanx,
B U D

ps. heres my formula:
if {field}<100000 then
&quot;0&quot;+
left(totext({field}),1)+&quot;/&quot;+
mid(totext({field}),2,1)+
mid(totext({field}),4,1)+&quot;/&quot;+
mid(totext({field}),5,2)

else if {field}>=100000 then

left(totext({field}),2)+&quot;/&quot;+
mid(totext({field}),3,1)+mid(totext({field}),5,1)+&quot;/&quot;+
mid(totext({field}),6,2)

 

[synapsevampire]

Normally a numeric date is stored as yyyymmdd, which allows for proper sorting, this design was poorly conceived as it does little more than try to save a few bytes, which in a big kid database would be negligible, if not less efficient.

Your code is OK, but I'd change it slightly:

if {table.date} < 100000 then
cdate(left(totext(60203),1)+&quot;/&quot;+
mid(totext({table.date}),2,1)+
mid(totext({table.date}),4,1)+&quot;/&quot;+
mid(totext({table.date}),5,2))
else if {table.date}>=100000 then

cdate(left(totext({table.date}),2)+&quot;/&quot;+
mid(totext({table.date}),3,1)+mid(totext({table.date}),5,1)+&quot;/&quot;+
mid(totext({table.date}),6,2))
else
cdate(1970,1,1)

Note that I add in an additional else at the end to set a default in case something isn't stored correctly.

This creates real date fields from the data.

-k

 

[TheMagikWand]

wow, thanks alot, just another quick Q tho, what exactly does adding the cdate()function do

and yes, it was poorly designed, and whats worse is that i dont have enough privilages to access it

thanx again,
B U D

 

[synapsevampire]

cdate creates a date field. You were creating text fields.

Once it's a date field, you can format, sort, group, do date differences, etc. - all of the great things people normally require from date fields.

-k

 

[TheMagikWand]

haha perfect, agian thanks for your help.

B U D

 

[losthorizon]

Here's what's in the Help file:
Action:
CDate (number) converts and returns a Date given a number which is the number of days starting from December 30, 1899.

CDate (string) converts and returns a Date given a string.

CDate (dateTime) converts and returns a Date given a DateTime value.

CDate (YYYY, MM, DD) uses the given arguments to create a Date value