The Best Cyprus Community

Skip to content


Crystal Reports

Anything related to the Internet, computers and technology in general.

Crystal Reports

Postby CBBB » Fri Dec 06, 2013 5:59 pm

Any of you Cyprus Forum Geniuses any good with Crystal Reports?

I've just started playing with it and have hit a brick wall with a data comparison.

The database I am querying stores dates as integers of the form YYYYMMDD so that 06/12/2013 is presented as the number 20,131,206!!!

As I don't want to have my users enter dates in that format, I need to convert either the database field to a normal date format or the user entered date to the database format, before I do my selection.

I've tried all sorts of stuff, but keep getting too complicated for CR.

Any suggestions anyone?
User avatar
CBBB
Leading Contributor
Leading Contributor
 
Posts: 11435
Joined: Tue May 20, 2008 1:15 pm
Location: Centre of the Universe

Re: Crystal Reports

Postby Get Real! » Fri Dec 06, 2013 6:20 pm

CBBB wrote:Any suggestions anyone?

Yes, learn basic SQL.
User avatar
Get Real!
Forum Addict
Forum Addict
 
Posts: 40448
Joined: Mon Feb 26, 2007 12:25 am
Location: Nicosia

Re: Crystal Reports

Postby Get Real! » Fri Dec 06, 2013 6:37 pm

Basically the logic to convert from user input variables (say "Day", "Month", "Year") to the string version (you called it integer) is something like this:

IntegerDate = LeadingZero(Year,4) + LeadingZero(Month,2) + LeadingZero(Day,2)

If you don’t understand this basic pseudocode above forget it! :lol:
User avatar
Get Real!
Forum Addict
Forum Addict
 
Posts: 40448
Joined: Mon Feb 26, 2007 12:25 am
Location: Nicosia

Re: Crystal Reports

Postby Get Real! » Fri Dec 06, 2013 6:50 pm

Here we go, SQL has a function that converts year, month, and day into Datetime! :)

http://stackoverflow.com/questions/1923 ... o-datetime

Have fun!
User avatar
Get Real!
Forum Addict
Forum Addict
 
Posts: 40448
Joined: Mon Feb 26, 2007 12:25 am
Location: Nicosia

Re: Crystal Reports

Postby CBBB » Sat Dec 07, 2013 7:35 am

Re GR! I was doing this stuff when you were still a little wombat! It is a specific solution for Crystal Reports that I am after. The logic is easy, it is just the implementation I am having trouble with.
User avatar
CBBB
Leading Contributor
Leading Contributor
 
Posts: 11435
Joined: Tue May 20, 2008 1:15 pm
Location: Centre of the Universe

Re: Crystal Reports

Postby Get Real! » Sat Dec 07, 2013 9:29 am

CBBB wrote:Re GR! I was doing this stuff when you were still a little wombat! It is a specific solution for Crystal Reports that I am after. The logic is easy, it is just the implementation I am having trouble with.

I don't think so mate, I doubt you had even seen a computer when I was a professional programmer because the answer to your question....

CBBB wrote:I need to convert either the database field to a normal date format or the user entered date to the database format, before I do my selection.

...is this:


From integer date to D/M/Y
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), IntegerDate))


From D/M/Y to integer date:
SELECT
CAST(
CAST(year AS VARCHAR(4)) +
RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
RIGHT('0' + CAST(day AS VARCHAR(2)), 2)
AS DATETIME)



Now if you don't know how to use this code that's another story! :lol:
User avatar
Get Real!
Forum Addict
Forum Addict
 
Posts: 40448
Joined: Mon Feb 26, 2007 12:25 am
Location: Nicosia

Re: Crystal Reports

Postby CBBB » Sat Dec 07, 2013 9:46 am

Get Real! wrote:
CBBB wrote:Re GR! I was doing this stuff when you were still a little wombat! It is a specific solution for Crystal Reports that I am after. The logic is easy, it is just the implementation I am having trouble with.

I don't think so mate, I doubt you had even seen a computer when I was a professional programmer because the answer to your question....

CBBB wrote:I need to convert either the database field to a normal date format or the user entered date to the database format, before I do my selection.

...is this:


From integer date to D/M/Y
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), IntegerDate))


From D/M/Y to integer date:
SELECT
CAST(
CAST(year AS VARCHAR(4)) +
RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
RIGHT('0' + CAST(day AS VARCHAR(2)), 2)
AS DATETIME)



Now if you don't know how to use this code that's another story! :lol:


They had computers that long ago in the outback?

When I started working as a professional programmer (after completing my Computer Science studies) in 1974, you were probably still wondering what the thing between your legs was for!

As I said, I know what to do, it is getting Crystal Reports to do it!
User avatar
CBBB
Leading Contributor
Leading Contributor
 
Posts: 11435
Joined: Tue May 20, 2008 1:15 pm
Location: Centre of the Universe

Re: Crystal Reports

Postby Get Real! » Sat Dec 07, 2013 9:58 am

CBBB wrote:They had computers that long ago in the outback?

When I started working as a professional programmer (after completing my Computer Science studies) in 1974, you were probably still wondering what the thing between your legs was for!

Programming in 1974 was probably LISP and FORTRAN which is as useful today as a Bernoulli drive is to an iPad! :lol:

As I said, I know what to do, it is getting Crystal Reports to do it!

:roll: I'll bet I can sort it in about 10 minutes if I was there, but I'm just gonna let you use FORTRAN to tackle the problem! :lol:

Bye-bye...
User avatar
Get Real!
Forum Addict
Forum Addict
 
Posts: 40448
Joined: Mon Feb 26, 2007 12:25 am
Location: Nicosia

Re: Crystal Reports

Postby CBBB » Sat Dec 07, 2013 10:53 am

Get Real! wrote:
CBBB wrote:They had computers that long ago in the outback?

When I started working as a professional programmer (after completing my Computer Science studies) in 1974, you were probably still wondering what the thing between your legs was for!

Programming in 1974 was probably LISP and FORTRAN which is as useful today as a Bernoulli drive is to an iPad! :lol:

As I said, I know what to do, it is getting Crystal Reports to do it!

:roll: I'll bet I can sort it in about 10 minutes if I was there, but I'm just gonna let you use FORTRAN to tackle the problem! :lol:

Bye-bye...


COBOLers to you!

I started using ALGOL 60 before I progressed to COBOL and Fortran.
User avatar
CBBB
Leading Contributor
Leading Contributor
 
Posts: 11435
Joined: Tue May 20, 2008 1:15 pm
Location: Centre of the Universe

Re: Crystal Reports

Postby supporttheunderdog » Wed Dec 11, 2013 10:22 am

CBBB wrote:
Get Real! wrote:
CBBB wrote:They had computers that long ago in the outback?

When I started working as a professional programmer (after completing my Computer Science studies) in 1974, you were probably still wondering what the thing between your legs was for!

Programming in 1974 was probably LISP and FORTRAN which is as useful today as a Bernoulli drive is to an iPad! :lol:

As I said, I know what to do, it is getting Crystal Reports to do it!

:roll: I'll bet I can sort it in about 10 minutes if I was there, but I'm just gonna let you use FORTRAN to tackle the problem! :lol:

Bye-bye...


COBOLers to you!

I started using ALGOL 60 before I progressed to COBOL and Fortran.



Come on boys, lets get back to BASIC.

C you soon.
User avatar
supporttheunderdog
Main Contributor
Main Contributor
 
Posts: 8191
Joined: Thu Oct 28, 2010 3:03 pm
Location: limassol


Return to Internet, Computers and Technology

Who is online

Users browsing this forum: No registered users and 0 guests