datepart() or totext() in SQL command?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15402
Printed Date: 28 Apr 2024 at 8:36am
Topic: datepart() or totext() in SQL command?
Posted By: acertik
Subject: datepart() or totext() in SQL command?
Date 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.
|
Replies:
Posted By: rkrowland
Date 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.
|
Posted By: acertik
Date 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
|
Posted By: rkrowland
Date 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.
|
Posted By: acertik
Date 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
|
Posted By: rkrowland
Date 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.
|
Posted By: acertik
Date 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
|
Posted By: acertik
Date 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
|
Posted By: rkrowland
Date 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}' ".
|
Posted By: acertik
Date 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.
|
Posted By: rkrowland
Date Posted: 26 Jan 2012 at 10:01pm
|
I did some quick googling and couldn't find much in the way of help.
It may be easier to go back to the original statement without syntax errors (= instead of like) and look at the correct methods for converting to varchar in pervasive.
A quick search returned convert((year({?parameter}), SQL_VARCHAR) instead of the cast function - however I can't help you with testing in this instance so all I can really do to help is throw suggestions at you and hope they work! haha :)
Regards,
Ryan.
|
Posted By: acertik
Date Posted: 27 Jan 2012 at 11:54am
Ryan:
Thank you very much for all your assistance. I think the best thing for me to do is further look into the proper Pervasive SQL syntax for the cast() function. It's probably off by one character or something silly like that.
You've given me some interesting new ideas. Thanks!
Alexander Certik
|
Posted By: acertik
Date Posted: 28 Jan 2012 at 2:12pm
Ryan:
I figured out the problem! I used your suggested code from before...
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right(cast(year({?parameter}) as varchar(4)),2)
The "&" needed to be replaced with a "+". Problem solved!
I appreciate all your assistance. Thank you very much.
Alexander Certik
|
Posted By: rkrowland
Date Posted: 29 Jan 2012 at 9:45pm
Originally posted by acertikRyan: I figured out the problem! I used your suggested code from before... select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right(cast(year({?parameter}) as varchar(4)),2)
The " &" needed to be replaced with a " +". Problem solved! I appreciate all your assistance. Thank you very much. Alexander Certik
Great! Glad you finally got it sorted! :)
Regards,
Ryan.
|
Posted By: acertik
Date Posted: 02 Feb 2012 at 5:12pm
Ryan:
Would you be able to break down the syntax of the cast function for me? I need to use a similar trick on another part of this report, but I don't quite understand what the 4 and the 2 represent.
I tried finding cast() syntax on Google, but can't find it anywhere.
Thank you for your help,
Alexander Certik
|
Posted By: rkrowland
Date Posted: 02 Feb 2012 at 10:17pm
|
The 4 and the 2 aren't relevant to the cast function, the 4 is related to the varchar() and states that the string we're converting is 4 characters long (2011). The 2 is related to the right() and basically it says of the string we've just converted to varchar we just want to return the 2 right most characters (11).
Brief details of each function;
RIGHT({String},{Number of characters})
{String} = CAST({Field to be Converted} as {Data Type to Convert To})
{Field to be Converted} = YEAR({datefield to extract year from})
{Data Type to Convert To} = VARCHAR({String Length})
{Number of characters} is the 2
{String Length} is the 4
{datefield to extract year from} is your {?dateparameter} field
Hopefully that clears up how it works, if you need any more assistance let me know.
Regards,
Ryan.
|
|