Date Format Conversions in SSIS

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.

Greg.

Date conversions and arithmetic

Hi folks,
New to SQL programming and reading about CONVERT, CAST and the DATE
functions aren't giving me enough info. to solve my problem.
My data looks like this:
20050416000000 Admitted 33
20050416000000 Discharged 26
20050416000000 Registered 70
20050417000000 Admitted 33
20050417000000 Discharged 21
20050417000000 Registered 65
Column I'm interested in Discussing is the first, which is stored in its
table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATETIM
E
or SMALLDATETIME.
The first 8 characters represent the yyyymmdd but it's just a string. The
trailing zeros stored seem to be junk.
I need todo the SELECT to get that column from today and only the previous
seven days. Also, I want it formatted in mm/dd/yyyy in my output if
possible.
This is the closest I've gotten:
Select DateID
from DispositionCount
WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
(DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
(DateID,7,2), DATEPART(day,GETDATE()))<= 7)
Can anyone tell me how to do this? Getting the DBA to change the column to a
proper datetime is not feasible.
Thanks,
hbcpTry This...
Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
DateName
From ...
Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
"hbcp_1" wrote:

> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATET
IME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
a
> proper datetime is not feasible.
> Thanks,
> hbcp|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe I'd do it this way - create a derived table that acts as my
own special View (since the DBA is so tight):
SELECT DateID, Status, Tally
FROM
(SELECT LEFT(DateID,8) As DateID, Status, Tally
FROM DispositionCount) As A
WHERE DateID BETWEEN CONVERT(VARCHAR(10),CURRENT_TIMESTAMP-7,112)
AND CONVERT(VARCHAR(10), CURRENT_TIMESTAMP,112)
Substitute the correct column names for the ones I used.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmVLHoechKqOuFEgEQJ1sACgtQThI8u3Vt5W
ILgEnFdsoU9d9V8An04W
o1H4Q/Gf0Z2e3AjCDugl6m/Q
=ykSG
--END PGP SIGNATURE--
hbcp_1 wrote:
> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATET
IME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
a
> proper datetime is not feasible.|||Thanks! Not quite, but I can see it's closer... some syntax error marking at
'And'?
Is DateName a constant or declaration of a variable on the fly?
"CBretana" wrote:
> Try This...
> Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
> DateName
> From ...
> Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
> "hbcp_1" wrote:
>|||Untested:
select Select DateID
from DispositionCount
WHERE cast(left(DateID,8) as datetime) >= convert(datetime, dateadd(d, -7,
getdate()), 112)
((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
cast(left('20050416000000',8) as datetime)
"hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
news:633B8157-4016-4BDF-A552-FE87BCA7C23F@.microsoft.com...
> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a
> DATETIME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
> a
> proper datetime is not feasible.
> Thanks,
> hbcp|||CBretana -- THANKS, just was missing a parenthesis...
Much obliged. Now I can backtrack through the help to learn what you taught
me. :-)
"CBretana" wrote:
> Try This...
> Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
> DateName
> From ...
> Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
> "hbcp_1" wrote:
>|||Thanks, will try all three suggestions. I'm not only using the query in SQL
Query Analyzer, it's actually being executed by another software product
(Java-based) and alas... the first option didn't work in that product. Need
to include the slashes too...
"Raymond D'Anjou" wrote:

> Untested:
> select Select DateID
> from DispositionCount
> WHERE cast(left(DateID,8) as datetime) >= convert(datetime, dateadd(d, -7
,
> getdate()), 112)
> ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> cast(left('20050416000000',8) as datetime)
> "hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
> news:633B8157-4016-4BDF-A552-FE87BCA7C23F@.microsoft.com...
>
>|||I was making a mistake elsewhere, I've got it perfect now. CBretana's works
best. It's exactly what I wanted and clean, efficient code. THANKS everyon
e.
"hbcp_1" wrote:
> Thanks, will try all three suggestions. I'm not only using the query in S
QL
> Query Analyzer, it's actually being executed by another software product
> (Java-based) and alas... the first option didn't work in that product. Ne
ed
> to include the slashes too...
> "Raymond D'Anjou" wrote:
>|||Since getdate() includes a time portion.
Also, a lot of people in this forum find it less intuitive to use Between
with dates:
(I'm not looking for a fight CBretana)
Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
DateName
From ...
Where Left(DateID, 8) >= CONVERT(DATETIME, DateAdd(day, -7, getdate()), 112)
And Left(DateID, 8) <= CONVERT(DATETIME, getdate(), 112)
"hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
news:1016A71C-28F8-4E9D-A51B-F1DACCEFFF96@.microsoft.com...
>I was making a mistake elsewhere, I've got it perfect now. CBretana's works
> best. It's exactly what I wanted and clean, efficient code. THANKS
> everyone.
>
> "hbcp_1" wrote:
>|||Yes, sorry, missing Parentheses!
"hbcp_1" wrote:
> CBretana -- THANKS, just was missing a parenthesis...
> Much obliged. Now I can backtrack through the help to learn what you taug
ht
> me. :-)
> "CBretana" wrote:
>

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Company -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004
is converted. This is based on the Lanaguage of the Login (unless overridden)
You can either override the date setting,
SET DATEFORMAT dm
Or use a neutral date formats (ISO and ISO8601
SET @.inputdate = '20040515' -- IS
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange results
With a DMY connection, it reads it as YDM, which no one uses
With a MDY connection, it reads it as YMD, with is the ODBC standard|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> > I need some help with Date Conversions. I am getting the following
error
> > when I run the query below.
> >
> > The conversion of a char data type to a datetime data type resulted in
an
> > out-of-range datetime value.
> >
> > declare @.inputdate char(10)
> > declare @.tempdate datetime
> > declare @.validdate char(10)
> >
> > SET @.inputdate = '15/05/2004'
> >
> > SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
> Company -
> > Dev$Base Calendar Change]
> > WHERE Date = @.InputDate and Nonworking = '1')
> > if @.tempdate is not null
> > SET @.validdate = '0' else SET @.validdate = '1'
> > select @.validdate
> >
> >
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Compan
y -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004'
is converted. This is based on the Lanaguage of the Login (unless overridden
).
You can either override the date setting,
SET DATEFORMAT dmy
Or use a neutral date formats (ISO and ISO8601)
SET @.inputdate = '20040515' -- ISO
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange r
esults.
With a DMY connection, it reads it as YDM, which no one uses.
With a MDY connection, it reads it as YMD, with is the ODBC standard.|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
error
an
> Company -
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>