Logical Date field in Stored Procedure is showing null value in report

[Savan]

Hello,

I have created a report which gets data from SQL Server using SP. in my sp i have created few logical date fields. 

Example:

DATEADD(Day,-6,'2017-02-17') As [Mon Date],

DATEADD(Day,-5,'2017-02-17') As [Tue Date], 
DATEADD(Day,-4,'2017-02-17') As [Wed Date],

DATEADD(Day,-3,'2017-02-17') As [Thu Date],

DATEADD(Day,-2,'2017-02-17') As [Fri Date], 
DATEADD(Day,-1,'2017-02-17') As [Sat Date]

this SP is having few parameters so in report i have created same report parameters to map them with parameters of SP. i executed the SP in SQL Server and it displays all data properly but when i run the report, the logical date fields are showing null value. i don't understand why it is showing null value.

[Stef]

Hi Savan,

You can use SQL Profiler to check the queries executed on previewing a report and if the string values are cast correctly into date values on the server. Test casting the string to date before executing the DATEADD function (T-SQL); otherwise, strings are cast to dates with the culture settings of the server machine:

select dateadd(day,2,CONVERT(VARCHAR(24),GETDATE(),6))

In addition, check if the SqlDataSource component is configured correctly to map SQL parameters (based on their type) to report parameters - How to: Connect to a SQL Database.

Regards,
Stef
Telerik by Progress

[Savan]

Hi Stef,

Thanks for the quick reply.

as i mentioned in above question that there are multiple parameters in the SP one of that parameter is of Date Type.

name of that parameter is @StartDate

so my Date Add is like this in the SP

DATEADD(Day,-6,@StartDate) As [Mon Date]

when user selects the parameters, Date is selected from Date Picker and passed as parameter to @StartDate in the SP.

i checked the profiler and it is executing properly and SqlDataSource is also properly connected and mapped with parameters.

 

Regards,

Savan

[Stef]

Hello Savan,

Please check if the SQL @StartDate parameter is mapped to a String report parameter, and if the passed string is in valid date format for the SQL server.
If you use a string report parameter, the DateTime object from the Date Picker will be cast to string based on the server machine's culture. this date format may differ from the SQL server date format.


In order to provide you more accurate suggestions, please post the SQL query used by the report, and the report's code allowing us to test locally its settings.

Regards,
Stef
Telerik by Progress