DTS/Bulk Insert and converting Date

[Ed Andres]

I have a txt file that is a fixed width with no delimiter and am trying to dump into a SQL table. The problem is, as the subject suggests, the date field. Here are a couple of lines of the txt file and the field lengths are:

6, 8, 6, 4, 4, 20

4300312/16/05 036032.3 UPSCUPS COLLECT ******
4300412/16/05 0383434.1UPSCUPS COLLECT ******
4300512/16/05 0335817.0UPSCUPS COLLECT ******

How do I get the second field (12/16/05) inserted as a date? Can I use a function like CDATE or CONVERT? and if so, where?

Sorry for the NOOB question.

 

[MartinCroft]

Does this help you along in the right direction

DECLARE @ImportString Varchar(50)

--Take example string not sure how you are importing


SET @ImportString='I4300316/08/05 036032.3 UPSCUPS COLLECT'

--show string before

SELECT @importstring

--To get a fixed point can use substring if not same point
can use other options to finf first / etc
--dependant on requirements see BOL for substring


SELECT Substring(@importstring,7,8)



--Use convert to get to datetime


SELECT CONVERT(datetime,Substring(@importstring,7,8))

 

[SQLBill]

Will there always be four numbers before the date?

Will the month portion always have two numbers (01, 02 ...12)/

Will the day portion always have two numbers (01, 02, ... 31)?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

And, is there a time part? DATETIME has both date and time. If you don't supply a time, SQL Server will use the default 00:00:00

-SQLBill

Posting advice: FAQ481-4875

 

[Ed Andres]

There are 6 characters before the date. In the example, there is a space then 5 numbers. There will always be 6 characters before the date.

The date part of the line will always be 8 characters long.

The month will always have 2 numbers

The day will always have 2 numbers

The time part is unimportant - the SQL default is fine.

Ed

 

[SQLBill]

Then I would use SUBSTRING, grab the date portion and CONVERT it to DATETIME. Just like MysticMart shows.

-SQLBill

Posting advice: FAQ481-4875

 

[Ed Andres]

My goal is to have this imported without a program. Can this be done with DTS? If so, where would the code go?

Thanks for your help!
Ed

 

[SQLBill]

I don't use DTS, so can't help with that.

-SQLBill

Posting advice: FAQ481-4875