{"id":237,"date":"2007-12-30T21:39:36","date_gmt":"2007-12-31T04:39:36","guid":{"rendered":"http:\/\/members.crystalreportsbook.com\/crystal-reports-xi\/1114-sql-expressions-reference\/"},"modified":"2011-10-12T18:19:53","modified_gmt":"2011-10-13T01:19:53","slug":"1114-sql-expressions-reference","status":"publish","type":"post","link":"http:\/\/www.crystalreportsonlinetraining.com\/training\/1114-sql-expressions-reference\/","title":{"rendered":"11.13 SQL Expressions Reference"},"content":{"rendered":"

SQL Expressions<\/h2>\n

Throughout this chapter we’ve discussed the different parts of a report that can be improved with SQL Expressions. The following table lists the different Crystal functions and their SQL Server equivalents. Although other databases are certain to have slight variations with their functions, they should be similar. You should consult your SQL documentation for the exact syntax of each function.<\/p>\n

Table 11-2. Converting Crystal functions to SQL functions.<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Crystal Function<\/th>\nSql Function<\/th>\n<\/tr>\n
CStr(), CBool(), CDbl(), CDateTime(), ToNumber(), ToText(), etc.<\/td>\nConvert(,)<\/td>\n<\/tr>\n
CurrentDate, CurrentTime(), PrintDate(), PrintTime()<\/td>\nCurDate(), CurTime(), Now()<\/td>\n<\/tr>\n
WeekdayName()<\/td>\nDayName()<\/td>\n<\/tr>\n
DatePart()<\/td>\nDayOfMonth()<\/td>\n<\/tr>\n
DayOfWeek()<\/td>\nDayOfWeek()<\/td>\n<\/tr>\n
DatePart()<\/td>\nDayOfYear()<\/td>\n<\/tr>\n
Hour()<\/td>\nHour()<\/td>\n<\/tr>\n
Month()<\/td>\nMonth()<\/td>\n<\/tr>\n
MonthName()<\/td>\nMonthName()<\/td>\n<\/tr>\n
DatePart()<\/td>\nQuarter()<\/td>\n<\/tr>\n
DatePart()<\/td>\nSecond()<\/td>\n<\/tr>\n
DatePart()<\/td>\nWeek()<\/td>\n<\/tr>\n
DatePart()<\/td>\nYear()<\/td>\n<\/tr>\n
Abs()<\/td>\nAbs()<\/td>\n<\/tr>\n
Cos(), Sin()<\/td>\nACos(), ASin()<\/td>\n<\/tr>\n
Tan(), ATn()<\/td>\nATan(), ATan2()<\/td>\n<\/tr>\n
Ceiling()<\/td>\nCeiling()<\/td>\n<\/tr>\n
Cos()<\/td>\nCos()<\/td>\n<\/tr>\n
Cot()<\/td>\nDegrees()<\/td>\n<\/tr>\n
Exp()<\/td>\nExp()<\/td>\n<\/tr>\n
Floor()<\/td>\nFloor()<\/td>\n<\/tr>\n
Log()<\/td>\nLog()<\/td>\n<\/tr>\n
<\/td>\nLog10()<\/td>\n<\/tr>\n
Mod<\/td>\nMod()<\/td>\n<\/tr>\n
Pi<\/td>\nPI()<\/td>\n<\/tr>\n
^<\/td>\nPower()<\/td>\n<\/tr>\n
<\/td>\nRadians()<\/td>\n<\/tr>\n
Rnd()<\/td>\nRand()<\/td>\n<\/tr>\n
Round()<\/td>\nRound()<\/td>\n<\/tr>\n
Sgn()<\/td>\nSign()<\/td>\n<\/tr>\n
Sin()<\/td>\nSin()<\/td>\n<\/tr>\n
Sqrt()<\/td>\nSqrt()<\/td>\n<\/tr>\n
Tan()<\/td>\nTan()<\/td>\n<\/tr>\n
Truncate()<\/td>\nTruncate()<\/td>\n<\/tr>\n
AscW()<\/td>\nAscii()<\/td>\n<\/tr>\n
ChrW()<\/td>\nChar()<\/td>\n<\/tr>\n
x & y<\/td>\nConcat(), x + y<\/td>\n<\/tr>\n
<\/td>\nInsert()<\/td>\n<\/tr>\n
LowerCase(), LCase()<\/td>\nLCase()<\/td>\n<\/tr>\n
Left()<\/td>\nLeft()<\/td>\n<\/tr>\n
Length()<\/td>\nLength()<\/td>\n<\/tr>\n
InStr()<\/td>\nLocate()<\/td>\n<\/tr>\n
TrimLeft(), LTrim()<\/td>\nLTrim()<\/td>\n<\/tr>\n
ReplicateString()<\/td>\nRepeat()<\/td>\n<\/tr>\n
Replace()<\/td>\nReplace()<\/td>\n<\/tr>\n
Right()<\/td>\nRight()<\/td>\n<\/tr>\n
TrimRight(), RTrim()<\/td>\nRTrim()<\/td>\n<\/tr>\n
<\/td>\nSoundEx()<\/td>\n<\/tr>\n
Space()<\/td>\nSpace()<\/td>\n<\/tr>\n
Mid()<\/td>\nSubString()<\/td>\n<\/tr>\n
UpperCase(), UCase()<\/td>\nUCase()<\/td>\n<\/tr>\n
<\/td>\nIfNull()<\/td>\n<\/tr>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

SQL Expressions Throughout this chapter we’ve discussed the different parts of a report that can be improved with SQL Expressions. The following table lists the different Crystal functions and their SQL Server equivalents. Although other databases are certain to have slight variations with their functions, they should be similar. You should consult your SQL documentation […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,1],"tags":[],"class_list":["post-237","post","type-post","status-publish","format-standard","hentry","category-chapter-11-maximizing-sql-performance","category-crystal-reports-xi","entry"],"_links":{"self":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts\/237","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/comments?post=237"}],"version-history":[{"count":4,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts\/237\/revisions"}],"predecessor-version":[{"id":1911,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts\/237\/revisions\/1911"}],"wp:attachment":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/media?parent=237"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/categories?post=237"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/tags?post=237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}