bcp invalid date format

Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruHi
/*
Kelly,Reynold,kelly@.reynold.com,5/30/2007 9:27:35 AM
John,Smith,bill@.smith.com,5/30/2007 9:27:35 AM
Sara,Parker,sara@.parker.com,5/30/2007 9:27:35 AM
*/
Make a column for dates as VARCHAR(50) and try insert
CREATE TABLE Tmp
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL,
dt varchar (100)
)
BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
SELECT * FROM Tmp
Later on you can convert VARCHAR to DATETIME datatype
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>|||On Nov 1, 4:47 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> /*
> Kelly,Reynold,ke...@.reynold.com,5/30/2007 9:27:35 AM
> John,Smith,b...@.smith.com,5/30/2007 9:27:35 AM
> Sara,Parker,s...@.parker.com,5/30/2007 9:27:35 AM
> */
> Make a column for dates as VARCHAR(50) and try insert
> CREATE TABLE Tmp
> (
> stFName varchar (50) NOT NULL,
> stLName varchar (50) NOT NULL,
> stEmail varchar (100) NOT NULL,
> dt varchar (100)
> )
> BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
> SELECT * FROM Tmp
> Later on you can convert VARCHAR to DATETIME datatype
> "Thiru.net" <thirudot...@.gmail.com> wrote in message
> news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have problem in BCP utility while exporting data to sql server.
> > It works in my system and my friend's system , but it gives the
> > following when its run in production
> > server.
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > BCP copy in failed
> > there are about 365000 records in the data file...
> > the date values go like below;
> > 5/30/2007 9:27:35 AM
> > 5/30/2007 9:28:37 AM
> > 5/30/2007 5:28:16 PM
> > 5/30/2007 5:28:32 PM
> > 5/30/2007 5:28:16 PM
> > please help me for this problem...
> > thanks,
> > Thiru- Hide quoted text -
> - Show quoted text -
Hi,
Thanks for your reply, actually I am using bcp command-lind utility
here. I cant change the datatype in the production server..
Thanks,
Thiru