Calculated DateTime Parameters Format Problem

Hi
I am having a lot of trouble getting reporting services to accept the
format of my datetime parameters. I keep getting a cannot convert char
string to small datetime errors either in visual studio when I preview
the report or if builds correctly there then it errrors out when run
through the report manager. I am developing these reports on a New
Zealand server which follows the British DateTime Format.
Initially I calculated the dates using datasets that calculated
various dates to populate a dropdown list by running the following
SQL statement, excuse the mess, This statement runs in query analyser
and in the reporting dataset editor without problems.
set DATEFIRST 1
select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
"Sort" = 1
UNION
Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
UNION
select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
getdate()),112),'Current Week', 3
UNION
select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
getdate())),112)),'Previous Week', 4
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
0),112),'Current Month',5
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
0),112),'Previous Month',6
UNION
Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
0),112),'Current Year',7
Order By Sort
It even runs in preview but when run from the report manager I get the
cannot convert varchar to small datetime error.
I retreated from this method and thought I would just use .net
calculated default values for the dates ie =DateTime.Today , this also
works in visual studio but falls over in the report manager on the
previously mentioned error.
All the dates are stored in tables in standard sqlserver datetime
format.
Could someone please provide a list of suggestions/bestpractices for
working with date parameters in SQL Server that are not in US format?
Thankyou
Michael BlackI would not convert the dates to varchar in your SQL. Instead I would format
them in the report in the format that you want.
"Mike" wrote:
> Hi
> I am having a lot of trouble getting reporting services to accept the
> format of my datetime parameters. I keep getting a cannot convert char
> string to small datetime errors either in visual studio when I preview
> the report or if builds correctly there then it errrors out when run
> through the report manager. I am developing these reports on a New
> Zealand server which follows the British DateTime Format.
> Initially I calculated the dates using datasets that calculated
> various dates to populate a dropdown list by running the following
> SQL statement, excuse the mess, This statement runs in query analyser
> and in the reporting dataset editor without problems.
> set DATEFIRST 1
> select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
> "Sort" = 1
> UNION
> Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
> UNION
> select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
> getdate()),112),'Current Week', 3
> UNION
> select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
> getdate())),112)),'Previous Week', 4
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
> 0),112),'Current Month',5
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
> 0),112),'Previous Month',6
> UNION
> Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
> 0),112),'Current Year',7
> Order By Sort
> It even runs in preview but when run from the report manager I get the
> cannot convert varchar to small datetime error.
> I retreated from this method and thought I would just use .net
> calculated default values for the dates ie =DateTime.Today , this also
> works in visual studio but falls over in the report manager on the
> previously mentioned error.
> All the dates are stored in tables in standard sqlserver datetime
> format.
> Could someone please provide a list of suggestions/bestpractices for
> working with date parameters in SQL Server that are not in US format?
> Thankyou
> Michael Black
>