October 31, 2008 at 7:49 am
Hi,
I need to convert a julian date (YYYYDDD) to dd mon yyyy format. Below is the sql which does the conversion
select
convert(varchar,
dateadd(d,
cast(right(rtrim(LTRIM(MT_EFF_DATE)),3) as int)-1,
cast(left(rtrim(LTRIM(MT_EFF_DATE)),4)+'0101' as date)
) ,
113) as the_date
from temp_ATPT_Layout_2
Using the above syntax, i am able to view the conversion from julian to dd mon yyyy.
However, i am not aware how to use the same in SSIS and update the target cloumn in the Target table.
To give a jist, we are transfering data from a flat file to Temp table. The datatype of all the columns in the flatfile is of varchar. The dates in the flat file are in julian date format. The data is first transfered to a RAW temp table. From the RAW temp table it needs to be transfered to a staging table having date datatypes in dd mon yyyy formats.
We need to know how to use the derived column to do the above transformation
Pls help
Regards
NIkhil
October 31, 2008 at 8:04 am
nikhil.pereira (10/31/2008)
Hi,I need to convert a julian date (YYYYDDD) to dd mon yyyy format. Below is the sql which does the conversion
select
convert(varchar,
dateadd(d,
cast(right(rtrim(LTRIM(MT_EFF_DATE)),3) as int)-1,
cast(left(rtrim(LTRIM(MT_EFF_DATE)),4)+'0101' as date)
) ,
113) as the_date
from temp_ATPT_Layout_2
Using the above syntax, i am able to view the conversion from julian to dd mon yyyy.
However, i am not aware how to use the same in SSIS and update the target cloumn in the Target table.
To give a jist, we are transfering data from a flat file to Temp table. The datatype of all the columns in the flatfile is of varchar. The dates in the flat file are in julian date format. The data is first transfered to a RAW temp table. From the RAW temp table it needs to be transfered to a staging table having date datatypes in dd mon yyyy formats.
We need to know how to use the derived column to do the above transformation
Pls help
Regards
NIkhil
Why dont store it in a proper datetime column?
select
dateadd(day,MT_EFF_DATE%1000-1,dateadd(year,MT_EFF_DATE/1000-1900,0)) as the_date
from temp_ATPT_Layout_2
Failing to plan is Planning to fail
November 1, 2008 at 12:06 am
The temp table would have raw data as is from the flat file. However, in the staging tables the datatypes would be converted for date from varchar to date.
WOuld like to know as to how do i use the sql statement provide by u to convert julian date to dd mon yyyy in ssis
November 4, 2008 at 2:23 am
Pls help.it is urgent
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply