Sql Server Reporting Services – Does Date Cover Entire Day?

 

Background

One of our users pleasantly gave me a list of things she will like to change in our Reports. The list includes whether we can assume entire day, when she presents us with a day.

Report Designer

Here is our Report’s parameter panel:

designer_20150710

 

Quick Explanation:

  • So for Date Start, she wants to indicate 7/10/2015
  • For Date End, she wants to leave as 7/10/2015, but assume entire day.  In computer parlance, 07-10-2015 23:59

 

SQL Profiler

Here is what SQL Server Reporting Services passes along to the DB.


   exec dbo.usp_DetailsForDateRangeWithMultipleValueParameters
            @dateStart='2015-06-10 00:00:00'
          , @dateEnd='2015-06-10 000:00:00'

Task

Our task is that for end dates, note not beginning dates, if the user has not included the time component we should add it in.

 

Preface

Customarily, to check if a value is same has the smaller datatype, one casts the original value to the constrained datatype.

This logic is used to determine if a float is int ( parseInt, trunc).

And, also when we see if a string matches a specific case.

Our scalar function casts the input date to a date datatype and compares the resultant value to the passed in date

  • When same, the passed in data does not a time component
  • When not same, we add 23 hours and 59 minutes to the passed in date-only input

 

SQL – User Defined Function


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[udf_assumeEndOfDay]') is not null
begin

    drop function [dbo].[udf_assumeEndOfDay]

end
go

/*

    drop function [dbo].[udf_assumeEndOfDay]

*/

IF OBJECT_ID (N'[dbo].[udf_assumeEndOfDay]') IS NULL
begin

    exec(
            'create function [dbo].[udf_assumeEndOfDay]()
                returns datetime
                begin

                    return null

                end  

            '
        )
end

GO

ALTER FUNCTION [dbo].[udf_assumeEndOfDay]
(
    @date datetime
)
RETURNS datetime
WITH SCHEMABINDING AS
BEGIN

    return
        (
            case
                when (@date = cast(@date as date)) then
                        dateadd(minute, 59, dateadd(hour, 23, @date))
                else @date
            end
        )

END;
GO

grant execute on [dbo].[udf_assumeEndOfDay] to [public]
go

Test


    declare @dateWithTime datetime

    set @dateWithTime = getdate()

    select
              [@dateWithTime] = @dateWithTime
            , [@dateWithTimeCalc] = [dbo].[udf_assumeEndOfDay](@dateWithTime)

    declare @dateWithoutTime datetime

    set @dateWithoutTime = cast(getdate() as date)

    select
              [@dateWithoutTime] = @dateWithoutTime
            , [@dateWithoutTimeCalc] = [dbo].[udf_assumeEndOfDay](@dateWithoutTime)

 

output

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s