Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 25th, 2004, 11:19 PM
Authorized User
 
Join Date: Jun 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to nlicata
Default HELP > bulk insert - need to truncate field

hi, thanks for the help...

MS-sql server and tab delimited text files.

I do a bulk insert and it works if I have column 5 to varchar in the table or format file. But column 5 is a date in format

24-Feb-2004 12:00:00 PM EST

Which is all great other then the EST at the end kills me. If datatype is smalldatetime then it says bad conversion during bulk insert. But if I edit text file manually and delete EST then it all works fine. I need field to stay date for querying later. Is there a way to trim from the left (LEFT in VBScript) during a bulk insert? Or is there a way or datatype to use?

thanks in advance..

Nick
 
Old February 26th, 2004, 10:50 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You could use the REPLACE SQL function. This seems to work...

REPLACE('24-Feb-2004 12:00:00 PM EST', 'EST', '')

Peter
------------------------------------------------------
Work smarter, not harder.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Bulk Insert snufse SQL Server 2000 9 December 17th, 2007 07:53 AM
bulk insert hymavathy_kr VB Databases Basics 1 May 17th, 2007 07:47 AM
BULK INSERT FAILS everest SQL Server 2000 0 February 23rd, 2006 12:55 PM
Bulk Insert luma SQL Server DTS 1 July 13th, 2005 01:48 AM
Bulk insert deyakhatib SQL Server 2000 6 March 4th, 2004 04:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.