none
Date Filter Issue

    Question

  • Hi all,

    I am creating a SQL Report on my local reporting services in my machine.

    My Report Require a date filter. I have created two parameters StartDate and EndDate.

    I put filter in dataset query as shown below -

    [CompanyName].[DateFieldName] BETWEEN (@StartDate) AND (@EndDate)

    The Issue is -

    1. My System date format is mm/dd/yyyy.

    2. As per my understanding SQL Stores dates in format - yyyy-mm-dd. (I think its the default format SQL Takes)

    While trying report I think my report render dates in my machine format and no records get displayed.

    Please suggest how it can be resolved.

    Thanks in advance.

    Regards,

    Saurav Dhyani

    Sunday, October 06, 2013 12:24 PM

Answers

  • Hi Saurav,
     
    According to your description, you think that the reason why your report did not returns any records is that the SQL Stored date format (yyyy-mm-dd) is different from System Stored date format (mm/dd/yyyy). In Reporting Service, if we do not format the date data, the date format in reports follows the window system's current culture's short date format. And after testing it in my own environment, the date data which is created in SQL will be automatically converted into system format when render the report. So the different date format will not affect the output of the report. Besides, we need to confirm that there exist data between the two date time when we filter the date field. And we can try to add a filter to filter the date field between @StartDate and @EndDate, instead of put it to dataset query. If it does not work, I think the report did not returns any records maybe result from other factors, for example, the establishment of date parameters.

    The following link is for your reference:
    http://technet.microsoft.com/en-us/library/aa337401(v=sql.105).aspx

    If I have something misunderstood, please provide us more information about the report. So that we can make further analysis and help you out.

    Thanks,
    Katherine Xiong

    • Marked as answer by Saurav.Dhyani Tuesday, October 08, 2013 2:32 PM
    Monday, October 07, 2013 7:59 AM

All replies

  • >> As per my understanding SQL Stores dates in format - yyyy-mm-dd. (I think its the default format SQL Takes)

    That's not correct, SQL stored the data internally in binary format and it takes the server settings when displaying the date value

    http://www.sql-server-performance.com/2004/datetime-datatype/.

    It is a good practice to pass the date time values in ISO formats and SQL will be able to interpret the correct date irrespective of "Regional settings"

    http://technet.microsoft.com/en-us/library/ms190977(v=sql.90).aspx

    http://msdn.microsoft.com/en-us/library/ms187819.aspx


    ISO 8601

    Description

    YYYY-MM-DDThh:mm:ss[.mmm]

    YYYYMMDD[ hh:mm:ss[.mmm]]

    Examples:

    • 2004-05-23T14:25:10

    • 2004-05-23T14:25:10.487

    To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

    The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format.

    The T indicates the start of the time part of the datetime value.

    The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

    Unseparated

    Description

    YYYYMMDD hh:mm:ss[.mmm]


    Satheesh
    My Blog


    Sunday, October 06, 2013 1:09 PM
  • Hi,

    Please find a simple report to get sample value from database using date filter in SSRS.

    You should be fine when pass the datatime value from SSRS as datetime type to SQL Server to fetch the record.

    Create table stg_table
    (
    crDate datetime default getdate(),
    value varchar(20)
    )
    Go
    Insert into stg_table(value)
    select 'today'
    
    GO
    
    create proc report1
    @datetime datetime
    AS
    begin
    select top 2 * from stg_table where 
    crDate >= CONVERT(varchar(10),@datetime,121)
    and crDate <=  CONVERT(varchar(10),@datetime,121)+' 23:59:59.000'
    
    End
    GO
    
    
    Exec report1 '2013-10-06'

    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Sunday, October 06, 2013 1:22 PM
    Sunday, October 06, 2013 1:20 PM
  • Datetime values are internally stored as two part integers. You should always use proper DATETIME datatype and express dates in unambiguous formats like YYYYMMDD or YYYY-MM-DDTHH:MM:SS

    Your where condition should be

    WHERE date_col>=@startdate and date_col<dateadd(day,1,@enddate)

    Sunday, October 06, 2013 3:40 PM
  • Hi Saurav,
     
    According to your description, you think that the reason why your report did not returns any records is that the SQL Stored date format (yyyy-mm-dd) is different from System Stored date format (mm/dd/yyyy). In Reporting Service, if we do not format the date data, the date format in reports follows the window system's current culture's short date format. And after testing it in my own environment, the date data which is created in SQL will be automatically converted into system format when render the report. So the different date format will not affect the output of the report. Besides, we need to confirm that there exist data between the two date time when we filter the date field. And we can try to add a filter to filter the date field between @StartDate and @EndDate, instead of put it to dataset query. If it does not work, I think the report did not returns any records maybe result from other factors, for example, the establishment of date parameters.

    The following link is for your reference:
    http://technet.microsoft.com/en-us/library/aa337401(v=sql.105).aspx

    If I have something misunderstood, please provide us more information about the report. So that we can make further analysis and help you out.

    Thanks,
    Katherine Xiong

    • Marked as answer by Saurav.Dhyani Tuesday, October 08, 2013 2:32 PM
    Monday, October 07, 2013 7:59 AM
  • Thanks all for your responses.

    Now I know how to play with report dates :)

    Tuesday, October 08, 2013 2:32 PM