Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an expression - how to concatinate

I'm creating an expression in a textbox in a table.
I would like to concatinate two expressions together.
What function can do this?Jim, you can use the ampersand (&) to concatinate items. For example,
fieldname & "/" & fieldname
"Jim" wrote:
> I'm creating an expression in a textbox in a table.
> I would like to concatinate two expressions together.
> What function can do this?sql

Creating a Calculated Field

I want to create a calculated field, but I can't get the expression to
work. All I want to do is the sum of field 1 and field 2. This must
be easy, but I just can't see it?On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> I want to create a calculated field, but I can't get the expression to
> work. All I want to do is the sum of field 1 and field 2. This must
> be easy, but I just can't see it?
You should be able to use something like this:
=Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
Field2Name.Value, "DataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > I want to create a calculated field, but I can't get the expression to
> > work. All I want to do is the sum of field 1 and field 2. This must
> > be easy, but I just can't see it?
> You should be able to use something like this:
> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> Field2Name.Value, "DataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
I entered that into the expression of the calculated field, but I get
the following error message.
A value expression used for the report parameter '=sum( Fields!
converganceValue.Value, "CapmLOB")
+ sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
aggregate function. Aggregate functions cannot be used in report
parameter expressions.|||Why don't you do it in your SQL query instead of doing it in the textbox
formula ?
SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
And then put the following in your tb formula.
=SUM(Fields!fld3.Value)
This is how I work and I think it's the real way to do it.
Good Luck
Julien
"Andy" <andywilliams1971@.msn.com> wrote in message
news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
>> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
>> > I want to create a calculated field, but I can't get the expression to
>> > work. All I want to do is the sum of field 1 and field 2. This must
>> > be easy, but I just can't see it?
>> You should be able to use something like this:
>> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
>> Field2Name.Value, "DataSetName")
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
> I entered that into the expression of the calculated field, but I get
> the following error message.
> A value expression used for the report parameter '=sum( Fields!
> converganceValue.Value, "CapmLOB")
> + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> aggregate function. Aggregate functions cannot be used in report
> parameter expressions.
>|||On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> Why don't you do it in your SQL query instead of doing it in the textbox
> formula ?
> SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> And then put the following in your tb formula.
> =SUM(Fields!fld3.Value)
> This is how I work and I think it's the real way to do it.
> Good Luck
> Julien
> "Andy" <andywilliams1...@.msn.com> wrote in message
> news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> >> > I want to create a calculated field, but I can't get the expression to
> >> > work. All I want to do is the sum of field 1 and field 2. This must
> >> > be easy, but I just can't see it?
> >> You should be able to use something like this:
> >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> >> Field2Name.Value, "DataSetName")
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
> > I entered that into the expression of the calculated field, but I get
> > the following error message.
> > A value expression used for the report parameter '=sum( Fields!
> > converganceValue.Value, "CapmLOB")
> > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > aggregate function. Aggregate functions cannot be used in report
> > parameter expressions.
Julien has a valid point.
If you are using the calculated field inside a table and there is only
one cell/value per item, you can most likely avoid using the aggregate
(i.e., =(Fields!
converganceValue.Value + Fields!nonConverganceValue.Value)); however,
if the calculated field is outside the table, etc then you will most
likely need to create the calculated field in the query or stored
procedure that is sourcing the report. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
>
>
> > Why don't you do it in your SQL query instead of doing it in the textbox
> > formula ?
> > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > And then put the following in your tb formula.
> > =SUM(Fields!fld3.Value)
> > This is how I work and I think it's the real way to do it.
> > Good Luck
> > Julien
> > "Andy" <andywilliams1...@.msn.com> wrote in message
> >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > >> > I want to create a calculated field, but I can't get the expression to
> > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > >> > be easy, but I just can't see it?
> > >> You should be able to use something like this:
> > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > >> Field2Name.Value, "DataSetName")
> > >> Regards,
> > >> Enrique Martinez
> > >> Sr. SQL Server Developer
> > > I entered that into the expression of the calculated field, but I get
> > > the following error message.
> > > A value expression used for the report parameter '=sum( Fields!
> > > converganceValue.Value, "CapmLOB")
> > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > aggregate function. Aggregate functions cannot be used in report
> > > parameter expressions.
> Julien has a valid point.
> If you are using the calculated field inside a table and there is only
> one cell/value per item, you can most likely avoid using the aggregate
> (i.e., =(Fields!
> converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> if the calculated field is outside the table, etc then you will most
> likely need to create the calculated field in the query or stored
> procedure that is sourcing the report. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer- Hide quoted text -
> - Show quoted text -
Thanks. I put it in the SQL code and it worked fine. Don't know why
I didn't think of that earlier.|||On Mar 21, 4:01 am, "Andy" <andywilliams1...@.msn.com> wrote:
> On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> > > Why don't you do it in your SQL query instead of doing it in the textbox
> > > formula ?
> > > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > > And then put the following in your tb formula.
> > > =SUM(Fields!fld3.Value)
> > > This is how I work and I think it's the real way to do it.
> > > Good Luck
> > > Julien
> > > "Andy" <andywilliams1...@.msn.com> wrote in message
> > >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > > >> > I want to create a calculated field, but I can't get the expression to
> > > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > > >> > be easy, but I just can't see it?
> > > >> You should be able to use something like this:
> > > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > > >> Field2Name.Value, "DataSetName")
> > > >> Regards,
> > > >> Enrique Martinez
> > > >> Sr. SQL Server Developer
> > > > I entered that into the expression of the calculated field, but I get
> > > > the following error message.
> > > > A value expression used for the report parameter '=sum( Fields!
> > > > converganceValue.Value, "CapmLOB")
> > > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > > aggregate function. Aggregate functions cannot be used in report
> > > > parameter expressions.
> > Julien has a valid point.
> > If you are using the calculated field inside a table and there is only
> > one cell/value per item, you can most likely avoid using the aggregate
> > (i.e., =(Fields!
> > converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> > if the calculated field is outside the table, etc then you will most
> > likely need to create the calculated field in the query or stored
> > procedure that is sourcing the report. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer- Hide quoted text -
> > - Show quoted text -
> Thanks. I put it in the SQL code and it worked fine. Don't know why
> I didn't think of that earlier.
You're welcome. Glad it worked.
Regards,
Enrique Martinez
Sr. SQL Server Developer