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
<< Prev Page  of 2
Author Message
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 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.
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet 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


Edited by acertik - 27 Jan 2012 at 11:54am
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet 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
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 29 Jan 2012 at 9:45pm
Originally posted by acertik

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
 
Great! Glad you finally got it sorted! :)
 
Regards,
Ryan.
IP IP Logged
acertik
Newbie
Newbie
Avatar

Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
Quote acertik Replybullet 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
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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.


Edited by rkrowland - 03 Feb 2012 at 1:04am
IP IP Logged
<< Prev Page  of 2
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.031 seconds.