Can't import date with midnight with 00:00!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Can't import date with midnight with 00:00!?

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2006-04-27 : 20:49:11

Hi ppl

I have a Date Time String Transformation to get a date in the format ddMMyyy hh:mm from a source text file to dd-MM-yyy hh:mm in my table. The source file has midnight as ddMMyyyy 00:00. I'm getting a package error stating " .... Cannot parse input data String beginning at '00'".

Any suggestion to overcome this issue?

Many thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 21:50:10

I assume that you are actually converting from character data to insert into a datetime column. To do this you need to convert the string into a format that SQL Server sees as unambiguous, like YYYYMMDD MM:SS


select
	Date =
	convert(datetime,
	substring(DS,5,4)+substring(DS,3,2)+left(DS,2)+right(DS,6))
from
	( select DS = '31122006 00:00' ) a

Results:

Date                                                   
------------------------------------------------------ 
2006-12-31 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources