July 29, 2003 at 3:04 am
hi all,
need to brake up a single date column into multiple columns..i am new into using DTS i would like to have some help regarding this..
My date column is in the form 01-Jan-03 and i need to transform it into day,week month quarter and year..can i find some sample code about this..or some example on how to brake a single col into multiple col's
i am in dire need for help for this..
thankyou to anyone in advance who can help me out..
July 29, 2003 at 6:44 am
Define your output columns on your target table.
Set transformations for all other matching columns.
Create ActiveX script to parse the input date to the separate output columns.
Far away is close at hand in the images of elsewhere.
Anon.
July 29, 2003 at 8:04 am
We do a lot of date cleansing within our DTS packages, but we use T-SQL to do it. Use the datepart function to do this. Example:
select datepart(yyyyy, MyDate) as TheYear,
datepart(qq, MyDate) as TheQuarter,
datepart(mm, MyDate) as TheMonth,
datepart(ww, MyDate) as TheWeek,
datepart(dd, MyDate) as TheDay
from MyTable
You can change this to an update statement and run it in a Execute SQL Task. Just add new columns to your table to hold the parts of the date.
Diane
July 30, 2003 at 7:06 am
I would suggest maintaining the date-time column, and creating a set of computed columns which return the values using the DATEPART function. This will allow you to do date arithmetic in the future, as well as view the attributes you need now.
July 30, 2003 at 12:01 pm
I am trying to do something simular inside my dts package. I am new to DTS as well. Inside one of my sql task properties. I am trying to delete from the table where my evendate field equals my global variable = 7/23/2003
The problem I have is that my eventdate field isnt in the proper format to do a comparison with the date given above.
? = paramter in property
parameter is gv_procdate
gv_procdate = 7/23/2003
Is there any reason why the following SQL Task property error's but It will work in SQL Query analyzer. When I run the DTS package and it gets to this particular SQL Task it error's out.
Invalid character value for cast specification
delete from dbo.stationall
where cast(datepart(mm,eventdate) as varchar(2))+ '/' + cast(datepart(dd,eventdate) as varchar(2))+ '/' + cast(datepart(yy,eventdate) as varchar(4)) = ?;
? = gv_ProcDate
gv_ProcDate = 7/7/2003
when in preview mode it isnt showing my plus signs before and after the '/' .
July 31, 2003 at 7:50 am
Put your delete code inside a stored procedure and call the sp from the Execute SQL Task, passing in the date value as a parameter.
Diane
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply