PAD function in Data Tranformation Editor

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.

David

I don't think we have your full statement... I'm not seeing where you convert to a date...

Never-the-less, why not substring the "date" field and then cast it to DT_DBDATETIME?|||Question, how is Jan 1st, 2006, represented?

112006? Or 1012006?|||1012006|||

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

|||

davidg12 wrote:

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

Okay, so then this should be easy... If you want to do this in one derived column (are you guaranteed that the value is a correct and verified date?), then you can do something like:

(DT_DBTIMESTAMP)(SUBSTRING(RIGHT("0" + datefield,8),5,4) + "-" + SUBSTRING(RIGHT("0" + datefield,8),1,2) + "-" + SUBSTRING(RIGHT("0" + datefield,8),3,2) + " 00:00:00")

In an upstream derived column, you could do the "RIGHT("0" + datefield,8)" calculation, and then just use that field in the above expression. It would make it easier to read.
|||

Thank you Phil. This works great.

David

Package doesn't finish, but no error

I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.

The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?

Any hints would be greatly appreciated.Hi Lee Gillie,

You might consider using a "For-each loop" instead with a variabel that changes value for each iteration...

Regards
Simon
|||Thanks Simon for considering this. I wasn't specific enough for you to see the 4 flags control running of 3 distinct packages, where one package is run with differening parameter setups. It would seem I would still need this kind of condition logic on the precedence within the loops to set up for each, even if there were but a single execute package task, and it is the condition on precedence that seems to be messing me up. It is not clear how the loop would avoid the need to have precedence control logic. Your response seems to presume that a simple expression to evaluate a boolean variable on a precedence does not work, and we are trying to avoid that? But perhaps my vision of implementing your idea is not right?

Thanks again, - Lee

Overlapping Records by DateTime

Hi guys and gals,
I need to pick your brains for a date/time series question. I'm trying to
write a query that will displays accounts, with their different account type
s
that overlap using the start and end dates.
So say I have the following records:-
AccountId - AccountType - Start - End
1 1 2006-01-01 2006-01-07
2 1 2006-01-06 2006-01-09
3 2 2006-01-02 2006-01-09
I can see that Account 1 and 2 are the same account type, but they overlap
by 1 day however account 3 is different and therefore is fine.
I need to write a query, to decipher all these account that overlap in date
of the same account type. This is part of a larger system, so you may wonde
r
why I wouldn't just place constraints to prevent this from happening, but th
e
reason is that I will allow accounts to overlap, and I have somewhere else i
n
the system a means to elect overlapped accounts based on merit which isn't
required in the query.
I've done some DDL and Inserts here, any help would be greatly appreciated.
Andy
CREATE TABLE Accounts(
AccountId int not null identity(1,1),
AccountType int not null,
UtcDateStart datetime not null,
UtcDateEnd datetime not null
)
CREATE INDEX PK_Accounts_AccountId
ON Accounts (AccountId)
GO
declare @.utcDateTime datetime
set @.utcDateTime = getutcdate()
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
SELECT * FROM AccountsIs this what you're after...
select
*
from
Accounts t1
join Accounts t2 on t1.AccountType = t2.AccountType
where
t2.AccountId > t1.AccountId
and (t1.UtcDateStart between t2.UtcDateStart and t2.UtcDateEnd
or t2.UtcDateStart between t1.UtcDateStart and t1.UtcDateEnd)
HTH. Ryan
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:893FFA23-53D8-4909-995D-C40BD71AF394@.microsoft.com...
> Hi guys and gals,
> I need to pick your brains for a date/time series question. I'm trying to
> write a query that will displays accounts, with their different account
> types
> that overlap using the start and end dates.
> So say I have the following records:-
> AccountId - AccountType - Start - End
> 1 1 2006-01-01 2006-01-07
> 2 1 2006-01-06 2006-01-09
> 3 2 2006-01-02 2006-01-09
> I can see that Account 1 and 2 are the same account type, but they overlap
> by 1 day however account 3 is different and therefore is fine.
> I need to write a query, to decipher all these account that overlap in
> date
> of the same account type. This is part of a larger system, so you may
> wonder
> why I wouldn't just place constraints to prevent this from happening, but
> the
> reason is that I will allow accounts to overlap, and I have somewhere else
> in
> the system a means to elect overlapped accounts based on merit which isn't
> required in the query.
> I've done some DDL and Inserts here, any help would be greatly
> appreciated.
> Andy
> CREATE TABLE Accounts(
> AccountId int not null identity(1,1),
> AccountType int not null,
> UtcDateStart datetime not null,
> UtcDateEnd datetime not null
> )
> CREATE INDEX PK_Accounts_AccountId
> ON Accounts (AccountId)
> GO
> declare @.utcDateTime datetime
> set @.utcDateTime = getutcdate()
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
>
> SELECT * FROM Accounts|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:578107
On Tue, 17 Jan 2006 01:41:03 -0800, Andy Furnival wrote:

>Hi guys and gals,
>I need to pick your brains for a date/time series question. I'm trying to
>write a query that will displays accounts, with their different account typ
es
>that overlap using the start and end dates.
>So say I have the following records:-
>AccountId - AccountType - Start - End
>1 1 2006-01-01 2006-01-07
>2 1 2006-01-06 2006-01-09
>3 2 2006-01-02 2006-01-09
>I can see that Account 1 and 2 are the same account type, but they overlap
>by 1 day however account 3 is different and therefore is fine.
>I need to write a query, to decipher all these account that overlap in date
>of the same account type. This is part of a larger system, so you may wond
er
>why I wouldn't just place constraints to prevent this from happening, but t
he
>reason is that I will allow accounts to overlap, and I have somewhere else
in
>the system a means to elect overlapped accounts based on merit which isn't
>required in the query.
>I've done some DDL and Inserts here, any help would be greatly appreciated.
Hi Andy,
Thanks for the DDL and the INSERTS! Made posting a breeze and answering
more fun.
In addition to Ryan's suggestion, here's another one that will work:
SELECT * FROM Accounts
go
SELECT *
FROM Accounts AS a
INNER JOIN Accounts AS b
ON a.AccountType = b.AccountType
AND a.AccountId > b.AccountId
AND a.utcDateStart < b.utcDateEnd
AND a.utcDateEnd > b.utcDateStart
The benefot of this version is that it avoids the use of OR. If the
utcDateStart and utcDateEnd columns in your real table are indexed, my
version will give the optimizer better opportunities to use that index.
Bottom line: test both for performance; choose the one that performs
best or (if there's no significant difference) the one that you find the
easiest to understand.
Hugo Kornelis, SQL Server MVP|||I would use a Calendar table and a query with a BETWEEN predicate. I
would also get a real key as an account_id with a check digit that
comforms to International banking standards instead of that silly and
dangerous IDENTITY pseudo-column.
As a matter of ISO-11179 conventions, the names should be
"start_utedate".
Use a BETWEEN predicate with a COUNT(*) > 1|||> instead of that silly and
> dangerous IDENTITY pseudo-column.
IDENTITY is a property of a column and NOT a column.
There is nothing stopping you creating a check digit based around IDENTITY
either.
There is nothing 'dangerous' about the IDENTITY 'property'.
The IDENTITY property can be successfully used to create surrogate keys or a
natural primary key where no other one may exist, for instance a message
board.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137635843.478530.301780@.f14g2000cwb.googlegroups.com...
>I would use a Calendar table and a query with a BETWEEN predicate. I
> would also get a real key as an account_id with a check digit that
> comforms to International banking standards instead of that silly and
> dangerous IDENTITY pseudo-column.
> As a matter of ISO-11179 conventions, the names should be
> "start_utedate".
>
> Use a BETWEEN predicate with a COUNT(*) > 1
>

overflow error

i m getting date like this 25-Dec-2006.....now i want to convert this date to 2006-12-25...

but i m getting erorr ?

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%',tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 4) and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=left(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) - 1) and
tbl_cams_uploaddetails.trade_date=convert(datetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2),126)
where tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism = 'EC'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89)
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

instead of your manual logic try to use the Convert(datetime, tbl_cms_uploaddetails.AdditionalField1)|||hi mani thanx for reply.....acyually in additionalfoeld1 i m getting string like this

1930454/IFG/25/DEC-06/15878....so after doing parsing i m getting date 25-Dec-2006......

|||

check all your rows retunrs the same value as you expected (only date)..

You can use the following expression...

Case When IsDate(parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)) = 1 Then

Convert(datetime, parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2))

Else NULL END

|||

try this, you can change you convert(datetime.. to convert(smalldatetime.. and you can also change the tbl_cams_uploaddetails.trade_date so that both have the same format

convert(varchar(10),tbl_cams_uploaddetails.trade_date,101)=convert(varchar(10),convert(smalldatetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)),101)

|||

i added this in my existing query..now it is returning 12 records......and when i run my actual query than it returns only 3 records....

why?

|||i tride yours also...getting same error message.|||

Remove the Group BY clause & validate your records ..

|||same result..3 and 12 records.|||hmm.. did you check you excel if it has dirty date values?
|||

How about rewriting the query as follow as...

select

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP

From

tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails WITH(NOLOCK) on

tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount

and tbl_cms_uploaddetails.AdditionalField1Like cast(tbl_cams_uploaddetails.Scheme_Code as varchar) + '/' +

tbl_cams_uploaddetails.folio_no+ '/' + Cast(day(tbl_cams_uploaddetails.trade_date) as varchar) + '/' +

Upper(Substring(Datename(MM,tbl_cams_uploaddetails.trade_date),1,3)) + '-' +

Cast(Year(tbl_cams_uploaddetails.trade_date) as varchar) + '/%'

Where

tbl_cams_uploaddetails.compare_status='Pending'

andPayment_Mechanism = 'EC'

and tbl_cms_uploaddetails.compare_status='Pending'

and Format_ID in (88,89)

group by

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date

|||now 5 minutes passed..still i m waiting for query result...still it is executing.|||

Its bcs of the expression may violate to use the index - the expression.

The operator LIKE also may cause this issue...

'1930454/IFG/25/DEC-06/15878' here what is 15878 stands for. Are you connecting this value also on join?

|||no i m not using this value 15878