change formating

I have a txt file that I import into my SQL2003 server. One of the items
is formated as 10/10/2005 13:23. That is the way teh file is sent to me.
I would like to be able to have it changed to just the 10/10/2005 format.
Is there an easy way to auto do that?
I currently have a tmp Table that the data gets readinto first, then it
updates the main table for any changes of exsisting records so there is room
for me to have somethign go in and modify the data.Hi
You don't say how you are loading this file?
You could treat this as two fields and ignore the second field (time part).
If you are using BCP or BULK INSERT then you can specify a format file and if
you are using DTS or the import wizard you can also specify that it should
ignore this data.
If you are loading into a datatime datatype you will always be holding the
time portion, but this will be 00:00:00 if you don't specify the time. A
different alternative (and probably slower!) if you are using datatime would
to update the data post insert.
John
"Johnfli" wrote:
> I have a txt file that I import into my SQL2003 server. One of the items
> is formated as 10/10/2005 13:23. That is the way teh file is sent to me.
> I would like to be able to have it changed to just the 10/10/2005 format.
> Is there an easy way to auto do that?
> I currently have a tmp Table that the data gets readinto first, then it
> updates the main table for any changes of exsisting records so there is room
> for me to have somethign go in and modify the data.
>
>|||I have a DTS package that is doing a bulk insert after reading teh text
file. Teh text file is camma delimted so I do not know how I would be able
to seperate teh time from teh date.
Here is a sample of the date being imported:
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977524ANJA","4",4,118,51.72,0.44,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977525ANJA","4",4,118,51.71,0.44,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977524ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
"ON HAND",12/19/2005
13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977525ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D5D46761-C4B7-4557-825A-774BC97B8425@.microsoft.com...
> Hi
> You don't say how you are loading this file?
> You could treat this as two fields and ignore the second field (time
> part).
> If you are using BCP or BULK INSERT then you can specify a format file and
> if
> you are using DTS or the import wizard you can also specify that it should
> ignore this data.
> If you are loading into a datatime datatype you will always be holding the
> time portion, but this will be 00:00:00 if you don't specify the time. A
> different alternative (and probably slower!) if you are using datatime
> would
> to update the data post insert.
> John
> "Johnfli" wrote:
>> I have a txt file that I import into my SQL2003 server. One of the
>> items
>> is formated as 10/10/2005 13:23. That is the way teh file is sent to
>> me.
>> I would like to be able to have it changed to just the 10/10/2005 format.
>> Is there an easy way to auto do that?
>> I currently have a tmp Table that the data gets readinto first, then it
>> updates the main table for any changes of exsisting records so there is
>> room
>> for me to have somethign go in and modify the data.
>>|||Hi
You can specify that the source file is comma delimited and if you use an
ActiveX transformation you can then you can specify something like:
DTSDestination("Col002") = LEFT(DTSSource("Col002"),8)
in the properties dialog (The properties button is on the general tab)
John
"Johnfli" wrote:
> I have a DTS package that is doing a bulk insert after reading teh text
> file. Teh text file is camma delimted so I do not know how I would be able
> to seperate teh time from teh date.
> Here is a sample of the date being imported:
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977524ANJA","4",4,118,51.72,0.44,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09635","7977525ANJA","4",4,118,51.71,0.44,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977524ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
> "ON HAND",12/19/2005
> 13:21:50,"01670136754","GILBERT","AEMNL0512012","09620","7977525ANJA","2",2,50,22.41,0.22,"N",,0,,0,,
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D5D46761-C4B7-4557-825A-774BC97B8425@.microsoft.com...
> > Hi
> >
> > You don't say how you are loading this file?
> >
> > You could treat this as two fields and ignore the second field (time
> > part).
> > If you are using BCP or BULK INSERT then you can specify a format file and
> > if
> > you are using DTS or the import wizard you can also specify that it should
> > ignore this data.
> >
> > If you are loading into a datatime datatype you will always be holding the
> > time portion, but this will be 00:00:00 if you don't specify the time. A
> > different alternative (and probably slower!) if you are using datatime
> > would
> > to update the data post insert.
> >
> > John
> >
> > "Johnfli" wrote:
> >
> >> I have a txt file that I import into my SQL2003 server. One of the
> >> items
> >> is formated as 10/10/2005 13:23. That is the way teh file is sent to
> >> me.
> >> I would like to be able to have it changed to just the 10/10/2005 format.
> >>
> >> Is there an easy way to auto do that?
> >> I currently have a tmp Table that the data gets readinto first, then it
> >> updates the main table for any changes of exsisting records so there is
> >> room
> >> for me to have somethign go in and modify the data.
> >>
> >>
> >>
>
>