Hi there,
My problem is that I have a DateTo dataset that receives a DateFrom
parameter. The default value of the DateFrom param is taken from a
DateDefault parameter value.
However, when I run the DateTo dataset, entering 27/03/2007, I get an
arithmetic overflow conversion error. If I enter US date 03/27/2007,
it works.
I am not sure why this happens because the dates use ISO (112) or ANSI
(102) formatting - I have tried with both of these.
I think it's to do with the conversion of dates, but I have not been
able to get a working version that does not use US dates.
DateFrom:
with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc
DateDefault:
with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte
DateTo:
with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
replace with '03/27/2007' succeeds
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc
I have attached a really simple RDL file that shows the problem. To
use it, simply change the datasource to any DB that has the standard
SSIS sysdtslog90 table in. This is automatically generated on the DB
when you turn on logging in SSIS to log to sql server.
Thanks in advance for any help!
RDL FILE
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="MyLoggingDB">
<DataSourceReference>MyLoggingDB</DataSourceReference>
<rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<ReportParameters>
<ReportParameter Name="DateFrom">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DateDefault</DataSetName>
<ValueField>DateValue</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Date From</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DateFrom</DataSetName>
<ValueField>DateValue</ValueField>
<LabelField>DateLabel</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<Height>5cm</Height>
</Body>
<rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DateFrom">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateFrom
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, DateFrom) as DateValue
,DateFrom as DateLabel
from
cte
order by
1 desc</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateDefault">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select max(starttime) as DateDefault
from dbo.sysdtslog90 with (nolock)
)
select
convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
from
cte</CommandText>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DateTo">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>with cte as (
select distinct convert(varchar, starttime, 112) as DateTo
from dbo.sysdtslog90 with (nolock)
where starttime >= @.DateFrom
)
select
convert(datetime, DateTo) as DateValue
,DateTo as DateLabel
from
cte
order by
1 desc</CommandText>
<QueryParameters>
<QueryParameter Name="@.DateFrom">
<Value>=Parameters!DateFrom.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>MyLoggingDB</DataSourceName>
</Query>
<Fields>
<Field Name="DateValue">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateValue</DataField>
</Field>
<Field Name="DateLabel">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DateLabel</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>To answer my own question and save others wasting time.
I have solved this issue now, by converting all the dates to use ISO
standard 20070327 format and pass this around to the parameters
instead of the full date.
Thanks
On 27 Mar, 17:54, "weelin" <wee...@.gmail.com> wrote:
> Hi there,
> My problem is that I have a DateTo dataset that receives a DateFrom
> parameter. The default value of the DateFrom param is taken from a
> DateDefault parameter value.
> However, when I run the DateTo dataset, entering 27/03/2007, I get an
> arithmetic overflow conversion error. If I enter US date 03/27/2007,
> it works.
> I am not sure why this happens because the dates use ISO (112) or ANSI
> (102) formatting - I have tried with both of these.
> I think it's to do with the conversion of dates, but I have not been
> able to get a working version that does not use US dates.
> DateFrom:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc
> DateDefault:
> with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte
> DateTo:
> with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom -- replace with '27/03/2007' fails --
> replace with '03/27/2007' succeeds
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc
> I have attached a really simple RDL file that shows the problem. To
> use it, simply change the datasource to any DB that has the standard
> SSIS sysdtslog90 table in. This is automatically generated on the DB
> when you turn on logging in SSIS to log to sql server.
> Thanks in advance for any help!
> RDL FILE
> <?xml version="1.0" encoding="utf-8"?>
> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/
> 2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/
> SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="MyLoggingDB">
> <DataSourceReference>MyLoggingDB</DataSourceReference>
> <rd:DataSourceID>518ad1ee-288c-4957-a44d-408e39de2244</
> rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>2.5cm</BottomMargin>
> <RightMargin>2.5cm</RightMargin>
> <PageWidth>21cm</PageWidth>
> <ReportParameters>
> <ReportParameter Name="DateFrom">
> <DataType>String</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DateDefault</DataSetName>
> <ValueField>DateValue</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>Date From</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>DateFrom</DataSetName>
> <ValueField>DateValue</ValueField>
> <LabelField>DateLabel</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>21cm</InteractiveWidth>
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ColumnSpacing>1cm</ColumnSpacing>
> <Height>5cm</Height>
> </Body>
> <rd:ReportID>fabcf51f-0565-4b9b-8a68-693ee7ea7e39</rd:ReportID>
> <LeftMargin>2.5cm</LeftMargin>
> <DataSets>
> <DataSet Name="DateFrom">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateFrom
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, DateFrom) as DateValue
> ,DateFrom as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateDefault">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select max(starttime) as DateDefault
> from dbo.sysdtslog90 with (nolock)
> )
> select
> convert(datetime, convert(varchar, DateDefault, 112)) as DateValue
> from
> cte</CommandText>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> </Fields>
> </DataSet>
> <DataSet Name="DateTo">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>with cte as (
> select distinct convert(varchar, starttime, 112) as DateTo
> from dbo.sysdtslog90 with (nolock)
> where starttime >= @.DateFrom
> )
> select
> convert(datetime, DateTo) as DateValue
> ,DateTo as DateLabel
> from
> cte
> order by
> 1 desc</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.DateFrom">
> <Value>=Parameters!DateFrom.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <DataSourceName>MyLoggingDB</DataSourceName>
> </Query>
> <Fields>
> <Field Name="DateValue">
> <rd:TypeName>System.DateTime</rd:TypeName>
> <DataField>DateValue</DataField>
> </Field>
> <Field Name="DateLabel">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>DateLabel</DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>16cm</Width>
> <InteractiveHeight>29.7cm</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>2.5cm</TopMargin>
> <PageHeight>29.7cm</PageHeight>
> </Report>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment