MS SQL Server - drop the time

Author

drop the time

Johnfli

2006-03-30, 8:23 pm

I have a field in my table that is set as DATETIME

The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM

I want to get rid of teh time part

How can I make it so the data that I already have in the table will drop teh
time??

John Bell

2006-03-31, 3:23 am

Hi

In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).

You don't say how the data is imported and if that is the only way the
column gets populated?

If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fires
for your bulk insert), or possibly have a computed column that truncates the
datetime.

John

"Johnfli" wrote:

> I have a field in my table that is set as DATETIME
>
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
>
> I want to get rid of teh time part
>
> How can I make it so the data that I already have in the table will drop teh
> time??
>
>
>

Johnfli

2006-03-31, 11:23 am

ummm, hmmmm, ok, so how do I do any of teh items you mentioned??? :)
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:CCF11FA2-4E88-4642-8242- AEA7213D590A@microso
ft.com...[color=darkred]
> Hi
>
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
>
> You don't say how the data is imported and if that is the only way the
> column gets populated?
>
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
>
> John
>
> "Johnfli" wrote:
>

John Bell

2006-03-31, 1:23 pm

Hi

I assume it is the same DTS package that will load the file?

Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.

John

Johnfli wrote:[color=darkred
]
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned??? :)
> I have DTS setup as teh text file is ftp'd to us. Teh time is included in
> the text file and teh people sending it to us are not interested in dropping
> off teh time portion.
>
>
>
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:CCF11FA2-4E88-4642-8242- AEA7213D590A@microso
ft.com...

Johnfli

2006-03-31, 1:23 pm

cool, I will give it a shot.
Thank you

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:1143826579.603455.255570@g10g2000cwb.googlegroups.com...
> Hi
>
> I assume it is the same DTS package that will load the file?
>
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
>
> John
>
> Johnfli wrote:
>

Sponsored Links