date time convert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date time convert

Author

Topic

shaw
Starting Member

15 Posts

Posted - 2007-04-30 : 20:21:54

hi there,

i have date.txt file with one column of dates and hours that looks like follows:

07/01/2001 01
07/01/2001 02
07/01/2001 03
07/01/2001 04
07/01/2001 05
07/01/2001 06

the query result gives a conversion error {ie, Bulk load data conversion error (type mismatch or invalid character for the specified codepage)} when I try to do the following:

USE database_name
BULK INSERT date
FROM 'c:\date.txt'

i guess it needs the data to look like this for it to be imported:

07/01/2001 01:00:00
07/01/2001 02:00:00
07/01/2001 03:00:00

can anybody help me with the script for importing this date.txt file to a date-time table(design:datetime) in the correct format?

PS: replace 01 with 01:00:00 doesn't work.

Thanks!

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-30 : 20:43:35

Try it with a space - ' 01' to ' 01:00:00'

/jeff

shaw
Starting Member

15 Posts

Posted - 2007-04-30 : 22:00:49

hey jeff,

i replaced them in excel, not in txt, but in the way you described.
it worked.

thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources