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

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
>