CODEWITHSUNDEEP
sql-servervisual-studiodatereporting-servicesbids
MiguelH
MiguelH

Reputation: 1425

SSRS query date format and parameter date formats differ?

BIDS/Visual studio 2005

Hi, I've had a look through SO Questions and cannot find an answer. I'm sure there must be one there somewhere.

This looks like a simple problem, but I've tried a number of variations but cannot find a solution!

Simple Dataset : "DateRange" to give me a dd/mm/yyyy hr:mn:ss format such as

FromDate                 ToDate
2016-04-24 00:00:00.000 2016-04-25 23:59:59.997

select (CAST(FLOOR(CAST(dateadd(d,-1,getdate()) AS FLOAT) ) AS DATETIME)) AS 'FromDate',
DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, -1, getdate()), 0)) as 'ToDate'

I then use FromDate and ToDate as parameters in the report as default values. However, these are then displayed in in the parameter fields as "mm/dd/yyyy hh:mn:ss" format!

My control panel settings are UK English. This works in the Visual Studio "Preview" but falls over on deploy as the date is not recognised! Any suggestions??

Upvotes: 0

Views: 1578

Answers (2)

Chris Latta
Chris Latta

Reputation: 20560

Each report has its own localization settings. In the Properties panel for the report you will see a Language property in the Localization section. You can set this to en-GB which should solve your problem. However, I generally set it to =User!Language to use the regional settings of the user.

Edit

Since that isn't working, let's convert to military date format (all elements in descending order of magnitude - year, month, day, hour, etc) as a string and let the application convert it back to a date. This format is the most robust way of passing dates around to avoid regional issues:

select CONVERT(VARCHAR(23), (CAST(FLOOR(CAST(dateadd(d,-1,getdate()) AS FLOAT)) AS DATETIME)), 121) AS 'FromDate',
CONVERT(VARCHAR(23), DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, -1, getdate()), 0)), 121) as 'ToDate'

Upvotes: 1

MiguelH
MiguelH

Reputation: 1425

The only solution I could get to work in the time available was to set the SSRS parameter as a string and convert the date to a string as follows:

select distinct left(convert(varchar, (CAST(FLOOR(CAST(dateadd(d,-1,mydate)   AS FLOAT) ) AS DATETIME)),120),30) as FromDate,
left(convert(varchar, DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, -1, mydate), 0)),120),30) as ToDate
from mydb
order by fromdate desc

The parameter is then CAST as a date in the query. A bit of a bother really!

Upvotes: 0

Related Questions