Crystal Reports: SQL For Extracting the Date Value in Crystal Reports

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize
	Buy Crystal Reports
	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: All CR Tools CR Analyzers CR Bestsellers DataBase Tools CR Graphics International CR Mail UFLs ReCrystallizePro CR Schedulers CR UFLs CR Viewers
	Add'l: About us Contact Us cViewSUITE Ppt Support CR trial
	Crystal Reports on Steroids

Crystal Reports: SQL to Extract the Date Values

We have discussed many useful things you can do with SQL expressions to improve the performance of your reports. We thought it would be great if we could extract the date value from a date time field to use in record selection with a date parameter.

Usually the time component messes things up, so it would be good to throw that away when doing the selection.

Create a SQL expression of

dateadd(day, datediff(day, 0, datefield), 0)

This can then be used in your SQL expressions, SQL commands or even your Views and Stored Procedures.

Now, you'll want to track and document what these SQL commands do. The perfect tool for that is:

You can more effectively analyze your Crystal Reports by using Report Analyzer:
http://www.crystalkeen.com/crystalreporttools/analyze/

With this powerful tool, you can:

Discover unused formulas, fields, and variables.
Identify group settings which cause blank pages and footers.

Identify missing standard Crystal documentation best practices (Report Title, Author, Description special fields).
Locate reports which may use the "Convert null field to default" setting inconsistently.
Keep versions of your documented reports for historical comparison.

Automatically detect reports that do not take advantage of server side database processing.

Detect record selection formulas which use data type conversion functions.

Detect group fields which use formulas instead of SQL Expression fields.

Find report tables which use less than an optimal number of fields.

Discover special fields which cause an additional pass over the report data source.

And so much more! Click here to see what you're missing by not having Report Analyzer.

Need to know more about SQL? Here's a list of SQL articles:

Crystal Reports: SQL Commands (2002)
Crystal Reports: SQL Commands for Creating Audit Records (2003)
Crystal Reports: SQL Designer (2001)
Crystal Reports: SQL Expressions and Subselects (2007)
Crystal Reports: SQL For Extracting the Date Value in Crystal Reports (2010)
Crystal Reports: SQL Generation to Change Selected Records (2003)
Crystal Reports: SQL Introduction (2001)
Crystal Reports: SQL Left Join (2004)

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.


	Questions? Please write to mark @ crystalkeen.com. We do want your business. Crystalkeen is a subsidiary of Mindconnection LLC. Crystal Reports is a subsidiary of Business Objects, which is owned by SAP. See the latest solutions from Crystal Reports. Use Business Objects products to turn reports into true business intelligence tools.