Joe,
The script you provided below worked like a champ, sorry for the long
delay, another project came up.

One last question. Now that i have seperated the date & time fields, I need
to remove the time portion of the date/time field.

So, instead of 2005-03-15 09:56:34, i want 2005-03-15. How can this be
done??

Thanks.


This is quite easy to achieve.

Not sure of you exact requirements, but this should be enough to head you in
the right direction.

If you text file has the format:

"Log Record One","2005-06-01 04:21:45"
"Log Record Two","2005-06-02 05:21:45"
"Log Record Three","2005-06-03 06:21:45"
"Log Record Four","2005-06-04 07:21:45"

To give you three field in a table, i.e.:

"Log Record One", "2005-06-01 04:21:45", "04:21:45"
"Log Record Two", "2005-06-02 05:21:45", "04:21:45"
"Log Record Three", "2005-06-03 06:21:45", "04:21:45"
"Log Record Four", "2005-06-04 07:21:45", "04:21:45"

Use a flat file source,an ole db destination and a transform data task
between them, delete the exitsting transformations and choose a new ActiveX
Script transformation.

You can then choose source and destination columns. When you've done that,
click properties to get you script editor open. And write something like the
following:


Option Explicit

Function Main()

DTSDestination("LogEntry") = DTSSource("Col001")
DTSDestination("LogEntryDate") = DTSSource("Col002")
DTSDestination("LogEntryTime") =
DATEPART("h",DTSSource("Col002"))&":"&DATEPART("n" ,DTSSource("Col002"))&":"&DATEPART("s",DTSSource(" Col002"))

Main = DTSTransformStat_OK

End Function

That should do you.

Let me know if it's not what your looking for.

Joe

CB wrote:
Convert the datetime into a string without the time portion and then cast the
result to datetime:

CAST(CONVERT(char(10),datetimecolumn,101) as datetime)

On Tue, 15 Mar 2005 21:04:56 -0500, Ed Enstrom wrote:

Here's another alternative:

CAST(FLOOR(CAST(datetimecolumn AS float) AS datetime)

It has some alternatives that can be even more fun. For instance, here's
how you can turn 2005-03-15 09:56:34 into 2005-03-15 09:00:00:

CAST(FLOOR(CAST(datetimecolumn AS float)*24)/24 AS datetime)