more expression syntax

Hi,

This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).

What I WANTED to do is a straight character check:

@.[User::xml_output] == "ABC"

but that wasn't working... so I deceided to try the len function.

However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.

len(trim(DT_WSTR,18,1252)@.[User::xml_output])) > 17

len(trim(@.[User::xml_output])) > 17

(DT_WSTR,18)@.[User::xml_output] > 17

I just want to trim @.[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.

Thanks,

Phil

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

I don't know if you're trying to do this in a data flow or a control flow, but here is the code for a control flow script task that gets the length of your variable and puts it into an integer variable named "length". You would of course need to list "xml_output" and "length" in the ReadOnlyVariables and ReadWriteVariables properties respectively.

Code Snippet

Public Sub Main()
Dts.Variables("length").Value = Dts.Variables("xml_output").Value.ToString.Length
Dts.TaskResult = Dts.Results.Success
End Sub

|||Thanks.|||

JayH wrote:

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

If you're referring to me, well, I'm always in favor of using the right tool for the job. That includes being efficient. This solutions works perfectly because his string is over 4,000 characters.|||

I figured you guys worked together or something

It's actually a huge shortcoming of expression syntax to not be able to include over 4000 characters. I was trying to evaluate some XML output and it was over the 4k limit and it basically always assumed it was under 17 characters because that's what my expression syntax was looking for either greater than or less than 17 characters. The less than would always hit because it just ignored the large size of the variable. Work-arounds are good, but in this case I think the language needs to adapt....

Phil

sql

Month/Year comparison against a date

Hi,

Is there a way to get the last day of a month, given a date in a datetime variable?

I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'

Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:

6/7/2006
7/15/2007

the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.

I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@.StartDate" and "@.EndDate" are the datetime parameters in the sp)

Month(ReportDate) > Month(@.StartDate) And Year(ReportDate) >= Year(@.StartDate) And
Month(ReportDate) <= Month(@.EndDate) And Year(ReportDate) <= Year(@.EndDate)

But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:

ReportDate > @.StartDate And ReportDate <= @.EndDate

Thank you.

Something like this:

Code Snippet

WHERE ( ReportDate >= dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )
)

You do NOT want to enclose ReportDate in a function since that will most likely result in not using indexing.

|||

WHERE ( ReportDate > dateadd( month, datediff( month, 0, @.StartDate ) , -1 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )
)

I just edited the previous reply to make it more close to your requirement..

|||

I don't think that is exactly 'right'. (I did make the assumption that the OP wanted ONLY JULY 2007 dates.)

Follow this code:

Code Snippet

DECLARE
@.StartDate datetime,
@.EndDate datetime

SELECT
@.StartDate = '06/7/2007',
@.EndDate = '7/15/2007'

-- Arnie's Variation
-- Starting at midnight, 7/1/2007, Ending at midnight, 8/1/2007
SELECT
'Arnie',
dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )

-- Mandip's Variation
-- Starting at midnight, 5/31/2007, ending at midnight, 8/1/2007
SELECT
'Mandip',
dateadd( month, datediff( month, 0, @.StartDate ) , -1 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )

-- OP Requested
-- the sp needs to select all records that come
-- after 6/30/2006 and on or before 7/31/2007

-- -
Arnie 2007-07-01 00:00:00.000 2007-08-01 00:00:00.000

Mandip 2007-05-31 00:00:00.000 2007-08-01 00:00:00.000

MONEY FORMAT presentation

Hi all,
I am tryng to locate the SQLServer vesion of Informix's DBMONEY env variable
which is used in the Informix env by tools like ISQL and I4GL to format
values stored as type MONEY into a nice presentation pattern. Is there such a
property for SQLServer?
Why am I looking for this? Basically the application that we are building
handles all this preso stuff at the front-end, problem is we are trying to
rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient is
used to seeing the numbers in a pre-formatted pattern as they currently use
ACCESS and the FORMAT function. Myself and my buddy are going slowly
blind/mad having to cast/convert values stored as money into strings to get
$'s and commas!
Slainte,
TaggartTaagart
DECLARE @.m AS DECIMAL (18,3)
SET @.m=100554545.36
SELECT convert(varchar,cast(@.m as money),1)
"Taggart" <Taggart@.discussions.microsoft.com> wrote in message
news:11E0F555-2BEF-4424-807C-3D6CEF31F6B7@.microsoft.com...
> Hi all,
> I am tryng to locate the SQLServer vesion of Informix's DBMONEY env
> variable
> which is used in the Informix env by tools like ISQL and I4GL to format
> values stored as type MONEY into a nice presentation pattern. Is there
> such a
> property for SQLServer?
> Why am I looking for this? Basically the application that we are building
> handles all this preso stuff at the front-end, problem is we are trying to
> rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient
> is
> used to seeing the numbers in a pre-formatted pattern as they currently
> use
> ACCESS and the FORMAT function. Myself and my buddy are going slowly
> blind/mad having to cast/convert values stored as money into strings to
> get
> $'s and commas!
> Slainte,
> Taggart
>
>|||Hi Uri,
That's much better, but still missing the $'s. Apart from hardwiring the $
sign and then concatanating this to the result, any ideas?
What I don't get is the need to cast a column that is already set as money
(pity I didn't use decimal!?) to money, maybe we are missing something
fundamental but isn't this just consuming CPU resource for no apparent reason?
As ex-informix guys we are somewhat puzzled by this and, on a similar point,
all the manipulation one has to undertake for datetime columns when you only
want to use the date portion - presumably something to do with no date
datatype in SQLServer, which is something we find very strange both in the
additional manipulation and also in terms of datastorage?
Slainte,
Taggart|||On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
>Hi Uri,
>That's much better, but still missing the $'s. Apart from hardwiring the $
>sign and then concatanating this to the result, any ideas?
Hi Taggart,
SQL Server is not intended to be used for formatting - that task is
usually handled by the front-end. As a result, SQL Server doesn't have
as much formatting features as some other tools.
If you're sure that your application is only used in dollar-using
countries, hardcoding the $ sign is probably the best solution. If the
app might be used all over the world, you'd be better off letting the
front-end determine the correct currency symbol from the useer's locale
settings and append that symbol to the amount.
>What I don't get is the need to cast a column that is already set as money
>(pity I didn't use decimal!?) to money, maybe we are missing something
>fundamental but isn't this just consuming CPU resource for no apparent reason?
There's no need for the extra CAST - Uri used it becuase the variable he
used in his example was not money. If your column is monmey, you can
just use
SELECT convert(varchar, Column_Name, 1)
>As ex-informix guys we are somewhat puzzled by this and, on a similar point,
>all the manipulation one has to undertake for datetime columns when you only
>want to use the date portion - presumably something to do with no date
>datatype in SQLServer, which is something we find very strange both in the
>additional manipulation and also in terms of datastorage?
Not having seperate date and time datatypes is indeed a pity.
However, there's no need for much manipulation to remove the time
portion from a datetime column. If you need the result as datetime (only
withoout time portion - or rather, with the default midnight time
portion), use
SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
And if you need it in character format (for presentation purposes), use
CONVERT with an appropriate style parameter and define the length of the
result such that the time will be cut off, for instance
SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo your use of convert is much. much neater than my attempt!
"Hugo Kornelis" wrote:
> On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
> >Hi Uri,
> >
> >That's much better, but still missing the $'s. Apart from hardwiring the $
> >sign and then concatanating this to the result, any ideas?
> Hi Taggart,
> SQL Server is not intended to be used for formatting - that task is
> usually handled by the front-end. As a result, SQL Server doesn't have
> as much formatting features as some other tools.
> If you're sure that your application is only used in dollar-using
> countries, hardcoding the $ sign is probably the best solution. If the
> app might be used all over the world, you'd be better off letting the
> front-end determine the correct currency symbol from the useer's locale
> settings and append that symbol to the amount.
> >What I don't get is the need to cast a column that is already set as money
> >(pity I didn't use decimal!?) to money, maybe we are missing something
> >fundamental but isn't this just consuming CPU resource for no apparent reason?
> There's no need for the extra CAST - Uri used it becuase the variable he
> used in his example was not money. If your column is monmey, you can
> just use
> SELECT convert(varchar, Column_Name, 1)
> >
> >As ex-informix guys we are somewhat puzzled by this and, on a similar point,
> >all the manipulation one has to undertake for datetime columns when you only
> >want to use the date portion - presumably something to do with no date
> >datatype in SQLServer, which is something we find very strange both in the
> >additional manipulation and also in terms of datastorage?
> Not having seperate date and time datatypes is indeed a pity.
> However, there's no need for much manipulation to remove the time
> portion from a datetime column. If you need the result as datetime (only
> withoout time portion - or rather, with the default midnight time
> portion), use
> SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
> And if you need it in character format (for presentation purposes), use
> CONVERT with an appropriate style parameter and define the length of the
> result such that the time will be cut off, for instance
> SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> --
> Hugo Kornelis, SQL Server MVP
>