MS SQL - Using DateTime as Date to compare dates

In MS SQL Server we don’t have a Date type. DateTime is using instead. When we had to compare dates that have also time part we can use one of these tricks:

1.Using DateTime as Date to compare dates

To compare 2 dates in SQL - convert them to date first. Use CONVERT to CHAR(10) and date format "101".

Example:

CONVERT(DATETIME, CONVERT(CHAR(10), CREATED_ON, 101)) <= CONVERT(DATETIME, CONVERT(CHAR(10), @dReportDate, 101))

2.Using DATEDIFF to compare dates (only if equal)

DATEDIFF function cannot be used for greater that or less that comparison because it returns only positive numbers but it can be used for checking if dates are equal.

Example:

DATEDIFF(d,@DATE1,@DATE2) = 0

when DATE1 = DATE2

Crystal Reports - Enhance the speed of Reports preloading

Initially when report is loading it calls all data sources (for e.g. all stored procedures with NULL values for parameters) to get the schema of the data result. Some stored procedures use NULL as parameter for showing all records and this initial call of stored procedures dumps the DB and make report loading slower.

The workaround is adding initial parameter @bIsRealIn by which to determine if the call was made by Crystal Report initialization or by user. Then when Crystal is initializing - return a predefined and typified dataset with one record (only a schema).

Note: It's strongly recommended to select the schema dataset from the real tables (that way all columns will be right typified) or CAST the select to the right data type. Otherwise the data in the report will be truncated or not treated right.

Example 1:

CREATE PROCEDURE [dbo].[Sel_WeeklySchedule]

@nCompanyID INT

, @nPatientID INT

...

, @bIsRealIn BIT

AS

SET NOCOUNT ON

DECLARE @tResult TABLE( WEEK_NO INT

, FULL_NAME NVARCHAR(200)

, D1 NVARCHAR(1000)

, D2 NVARCHAR(1000)

, D3 NVARCHAR(1000)

, D4 NVARCHAR(1000)

, D5 NVARCHAR(1000)

, D6 NVARCHAR(1000)

, D7 NVARCHAR(1000)

)

IF @bIsRealIn = 0

BEGIN

SELECT WEEK_NO

, FULL_NAME

, D1

, D2

, D3

, D4

, D5

, D6

, D7

FROM @tResult

END

ELSE BEGIN

...

...

SELECT WEEK_NO = @nWeekNo

, FULL_NAME = @sFullName

, D1 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D2 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D3 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D4 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D5 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D6 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D7 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

FROM T100_SCHEDULE

END

GO

Example 2:

CREATE PROCEDURE [dbo].[Sel_WeeklySchedule]

@nCompanyID INT

, @nPatientID INT

...

, @bIsRealIn BIT

AS

SET NOCOUNT ON

IF @bIsRealIn = 0

BEGIN

SELECT WEEK_NO = 0

, FULL_NAME = CAST('' AS NVARCHAR(150))

, D1 = CAST('' AS NVARCHAR(1000))

, D2 = CAST('' AS NVARCHAR(1000))

, D3 = CAST('' AS NVARCHAR(1000))

, D4 = CAST('' AS NVARCHAR(1000))

, D5 = CAST('' AS NVARCHAR(1000))

, D6 = CAST('' AS NVARCHAR(1000))

, D7 = CAST('' AS NVARCHAR(1000))

, LAST_UPDATE = GETDATE()

FROM @tResult

END

ELSE BEGIN

...

...

SELECT WEEK_NO = @nWeekNo

, FULL_NAME = @sFullName

, D1 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D2 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D3 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D4 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D5 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D6 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, D7 = dbo.Sel_WeeklySchedule_Day (@nCompanyID, @nPatientID, ...

, LAST_UPDATE

FROM T100_SCHEDULE

END

GO

This enhancement will solve the "Invalid group condition." problem for reports using groups and complicated stored procedures datasource.

Crystal Reports - Apply changes of DB source to the Report

To apply immediately the DB changes (new fields added, field datatype changed, etc.) to the report source:

• Go to Field Explorer

• Right click Database Fields

• Verify Database

When XSD data files will be used as a DB source for report – first logoff all “online” datasets with following steps:

• Go to Field Explorer

• Right click Database Fields

• Log On/Off Servers

• Project Data

• Current Connections

• Choose all available items

• Log Off
  

Then Verify Database - first 3 steps.

Crystal Reports - How to remove old DB source from Report or Subreport

To remove an old DB source from report or subreport - there should be no fields in use from these source. If there are still red-checked fields in the source - follow:

• Remove all fields of these source from all report sections

• Remove all fields of these source from all Formula Fields

• Remove all fields of these source from all Fields formula properties

• Remove all fields of these source from all Groups and Group custom names

• Remove all fields of these source from all Records and Group Selection Formula (Right click - Report - Edit Selection Formula - Record.../Group...)

• Remove all links to subreport and close all open subreports (Right click - Close Subreport)

Note: If you try to remove a source which has a fields in use (red-checkmarked and showing confirmation when removing) - then your Visual Studio will crash without save your last changes. In that case try to search a temp copy of your report in the same folder. Always SAVE before removing a data source.