Urgent!! Problems with the Date Conversions

[yjoke]

Hi there. I've been having some (lots of) problems in a project that I'm working on. I'm using an SQL 2000 database, Visual Basic 6.0 SP5, and Crystal Reports 8.5 Pro.
One of the problems that I'm facing is in converting a date from an Integer (in SQL 2k,who is the number 37317) to a date(dd-mm-yyyy) that needs to appear in a report (CR) through VB.
Is possible to convert the number 37317 in a crystal date format(yyyy-mm-dd)??
I've tryed inumerous options and ways with no luck. Is there anyone out there who can give me a hand!? ...
I tryed the link:

http://support.crystaldecisions.com/library/kbase/articles/c2001866.asp

but when I apply it in the formula editor, I get the message: " A statement is expected here ". I don´t know what that statement is.
Please help me again with any suggestion.

Is very urgent....
Any tips are welcome and apreciated!
thanks..

 

[dgillz]

Unless 37317 represents the 317th day of 1937, I do not believe you want to use the solution referenced on your link. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[yjoke]

OK!!

So what do you suggest as a possible solution....

Please help me!!

 

[Roadkill150]

could you post how you expect the integer 37317 to display on the report? It might help with the answers Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[yjoke]

I'm trying to format the integer "37317" that is on the date field on SQL2000,to a normal date string format as "yyyy-mm-dd" to show on a report.
I'd like to know how that is possible and what's the formula to make that possible.
Please help..it's urgent..!!!!

Thank's

 

[Roadkill150]

Ok.. what i was asking for how is 37317 supposed to represented in the date field? is it 1937,03,17 ? if this is the case you should be able to parse out the field and use it for your date formula, otherwise please explain how you would want the 37317 displayed (using the actual number provided) Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[Guest_imported]

You may have a situation in which the number represents a number of day, minutes or seconds fom a particular date. Resolving a date from this type of data is a real pain. If the number you have stored is 37317 it may represent the number of days starting from 01/01/1900 that would resolve to a date somewhere around 03/27/2002 or 37317/365(not accounting for leap years). One method I have found that help is if you can insert a dummy entry into the application feeding your data and see what number is populated based upon the current date. I work with one system which stores dates as the number of seconds since 01/01/1980, don't ask who though that was a good idea.

Good Luck

 

[yjoke]

Can you be more specific about the syntax of the formula??

Thanks..

 

[dgillz]

yjoke-

If you enter 37317 in excel and format it as a date you get 03/02/02. That happens to be 37,317 days since January 1st, 1900.

Assumming this is the type of data you are working with, use a formula like this:

Dateadd("d",{fieldname},date(1900,1,1))

Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[yjoke]

thaks its working!!

 

[yjoke]

None of the formulas all of U suggested worked.
So, after a long quest chase "the Holy Grail" I've come up with a solution to my problem.
I use the following formula:

date(1899,12,30) + ({the_date_field_in_question})

This formula worked for me.

 

[kenhamady]

yjoke,

Glad you found your solution. It is actually the same as dgillz' solution (adding your integer to a fixed date). The difference is that his example started with a different starting point. You can use either syntax. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com