How to make CR recognize "0" in dates

[TheMagikWand]

hey guys, im still new so this may seem easy but...

i have a database that lists dates in 6-digit YYMMDD format
(031214 = Dec. 14th 03 etc.) but everytime i use a year from later that 1999 i get a single digit year instead of a 2 digit year exe: 031214 = 12/14/3

can anyone help me add a zero to the year field????
(p.s. its a number field so just simply adding a zero by editing the text just wont work)

thanx,
B U D

 

[pgtek]

hi

click on your field then format choose cutomize and pick the one you are looking for and uncheck suppress leading zero and then click ok

cheers

pgtek

 

[dgillz]

First, either add 20000000 or 19000000 to your number to make it a YYYYMMDD number with an if-then, then convert it to a real date with NumberToDate().

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[TheMagikWand]

i tried to uncheck the supress leading zero, but in the format/customize menu there was no such option, im running CR 8.5 is there a chance that the version does not have this option or the option is in a differnt place?????

 

[TheMagikWand]

i am using the numbertodate(x) formula and i believe this is why its cutting off the first "0" digit, tho i havent tried added 2000000 or 1900000 to it. but wouldnt that mean id have to add either of those #'s to each individual database entry???????

 

[crystalreporting]

Nope - I think dgillz is suggesting this;

NumberToDate(2000000 + {table.datefield})

Peter Shirley

http://www.linkcorp.com

 

[TheMagikWand]

ah, but i do have several dates in my database that are pre-2000, would that require an additional part to the formula? or would that corretly fix both?

 

[crystalreporting]

What I don't understand is why the date is being cut off - I use the NumberToDate function all the time - it is designed to work with numeric fields that have this format; CCYYMMDD, so maybe it's getting confused with the no century deal? In either case, I think adding the century portion to the number BEFORE using NumberToDate will work. On another note, if your dates can contain zeros, you might want to include an if clause in your formula. The numeric dates in the database I work with can be zero, and NumberToDate converts this to a funky date e.g. 11/30/-2.

Peter Shirley

http://www.linkcorp.com

 

[crystalreporting]

Do you have database examples of both? You'll probably have to take that into account in your formula somehow to add 2000000 to 2000+ dates and 1900000 to 1999 and earlier dates.

Peter Shirley

http://www.linkcorp.com

 

[TheMagikWand]

i also have no clue why its cutting off the 0 in the date when using the numbertodate(x) formula, but i dont see how adding the CC to the date could hurt me.

thx for all your help guys

 

[dgillz]

Magik, if you cannot get this to work let us know. I thinkl you should be able to come up with an if-then to either add 20000000 or 19000000, adn the formula should work fine

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[TheMagikWand]

yea, it was quite easy after the tips, took about 2 mins to get the if-then to work properly, but now im running smoooooth.

again, thanks for all your help guys/gals

sincerely,
B U D