Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: datepart() or totext() in SQL command? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Topic: datepart() or totext() in SQL command?
    Posted: 18 Jan 2012 at 3:04pm
I'm using Crystal Reports 2008.

Is there a way to use the totext() function or the datepart() functions inside an SQL command? No matter what I try, I seem to get the syntax wrong and I cannot find any help information anywhere that addresses this.

Anybody know if this is possible?

I know this is possible inside a record selection formula; however, I need to be able to get unwanted records out before I get to the record selection stage within the report. Hopefully this makes sense.

Thank you to anyone who might have the answer.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 19 Jan 2012 at 4:07am
You'll need to use the SQL variations of those functions for them to work in a command.
 
DATEPART works pretty much identically;
 
Crystal Syntax (For Days)
DATEPART("d", {command.datefield})
 
SQL Syntax
DATEPART(dd, table.datefield)
 
TOTEXT however will be different;
 
Crystal Syntax
TOTEXT(command.field)
 
SQL Syntax
CAST(table.field as varchar)
or
CONVERT(varchar, table.field)
 
Regards,
Ryan.
 


Edited by rkrowland - 19 Jan 2012 at 4:11am
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Posted: 19 Jan 2012 at 3:08pm
Ryan:

Thanks for your reply. Unfortunately, this did not work. I still get a syntax error. Maybe I should have been more specific and mentioned that I am trying to link this function to a parameter, which I created successfully within the command.

Here's what I currently have in my command:

select * from rateplan where roomtype not in ('MASTER', 'ALL') and rateset = 'RK12'


I would like the "rateset" part to be comprised of the words "RK" plus the last two digits of whatever year the user types in. (RK12 for 2012, RK13 for 2013, etc...)

I imagine in order to do this properly, I would need to use either cast() or convert(), since the "rateset" field is set as a string field in the database. I'm pretty sure datepart() converts the data to an integer, which might cause further problems; but maybe I'm wrong.

Any suggestions? Or is this even possible to do with a parameter?

Thank you,

Alexander Certik



Edited by acertik - 19 Jan 2012 at 3:08pm
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 19 Jan 2012 at 10:44pm

Do the users enter just a year in your paramter? Or a date?

If they just enter a year, make your parameter datatype string and the following should work;
 
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right('{?parameter}',2)
 
 
If they enter a full date (datatype datetime), the following should work;
 
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right(cast(year({?parameter}) as varchar(4)),2)
 
I've haven't actually tested any of these with tables on my database, they should work but let me know if not and I'll try them against tables in my database.
 
Regards,
Ryan.


Edited by rkrowland - 19 Jan 2012 at 10:47pm
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Posted: 20 Jan 2012 at 12:47pm

Looks like we're on the right track! Thank you!

There is still a problem, however... instead of getting a syntax error, I am now getting an error that reads "Incompatible type in expression." I am assuming it's because of a data type mismatch, but I'm not sure where?

By the way, the user types in a full date, so there is a date parameter in place. I just need it to extract the last two digits of the year from that date, so I used example 2 in your last post.


Thanks for all your help,


Alexander Certik

IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 22 Jan 2012 at 9:58pm
Try the following;
 
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset like ('RK' + right(cast(year({?parameter}) as varchar(4)),2))
 
Regards,
Ryan.
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Posted: 23 Jan 2012 at 6:37pm
Thanks Ryan. I'll be back in the office tomorrow so I'll try that and let you know what happens.

Alexander Certik


Edited by acertik - 23 Jan 2012 at 6:38pm
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Posted: 25 Jan 2012 at 12:01pm
Ryan:

Unfortunately I'm now getting a syntax error again with this latest suggestion.

Any other advice?

Thank you for all your help,

Alexander Certik
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 26 Jan 2012 at 12:56am
Hmm I'm unsure what the syntax error could be, the above still works fine for me.
 
Where does it say the syntax error is near?
 
Which management system are you using? I only have experience with MS SQL Server, if you're using Oracle or something else it could be due to that.
 
A couple of things to try on the above which may work, replace the "+" with "&" and/or replace " {?parameter} " with " '{?parameter}' ".
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet Posted: 26 Jan 2012 at 12:03pm
Hmm. I didn't know the syntax was different between the different systems. I thought it was all internal within Crystal Reports. We use Pervasive as out database management tool.

The syntax error I get appears to be right after like and just before the (.

Maybe it's just a matter of getting the SQL syntax correct for Pervasive? If that's the case, you've given me an idea of something to look into, unless you have any other thoughts.

I tried all your suggestions and they didn't work.

Thank you.


Edited by acertik - 26 Jan 2012 at 12:03pm
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.008 seconds.