Insert a text column from a flatfile into a Datetime Column in a Table

Question

Post reply

Insert a text column from a flatfile into a Datetime Column in a Table

rbajimaya

SSC Rookie

Points: 30
More actions
April 18, 2010 at 6:54 pm

#233988

Dear all,
I have a flatfile that has a Column that has text in 'ddmmyyyy' format. When I try to Import this into my Table that has a Column defiend as datetime it fails.
How can I resolve this. Thx.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Buddy, how are u trying to import it? BCP , BULK insert, SSIS package, how?

rbajimaya SSC Rookie Points: 30 More actions · Answer 2

rbajimaya

SSC Rookie

Points: 30

April 18, 2010 at 9:12 pm

#1153561

I am using DTS Import. Thx.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Import it into your staging table as character based information and convert it when you transfer it to the final table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

I second Jeff.. any import from a flatfile should be put up in some holding table which will have nvarchar or varchar columns.. then apply your business logic and insert them into the staging table from where u can process the records...

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

Exactly how is it failing? It could be that the language settings for your SQL database aren't interpreting the ddmmyyyy format correctly.

rbajimaya SSC Rookie Points: 30 More actions · Answer 6

Thx for the Help guys.

I used the file - temp-table - real-table approach.

ddmmyyyy field was defined as a char(8) on the temp-table. All the fields from the flat file were simply 'copied' using DTS into the temp-table.

then, this sql cmd is run:

SET dateformat ymd

INSERT INTO [DSFM_Forecast]

SELECT DSFM_Forecast_tmp.Stock_Code, DSFM_Forecast_tmp.Customer_Group,

DSFM_Forecast_tmp.Whse_Code, 'SYS' AS FC_Type,

DSFM_Forecast_tmp.FC_Qty, stock_master.stk_std_cost * DSFM_Forecast_tmp.FC_Qty AS FC_Qty$_StdCost,

SUBSTRING(DSFM_Forecast_tmp.FC_Date, 5, 4) + '-' + SUBSTRING(DSFM_Forecast_tmp.FC_Date, 3, 2) + '-' + '1' AS YYYYMMDD,

SUBSTRING(DSFM_Forecast_tmp.FC_Date, 5, 4) + SUBSTRING(DSFM_Forecast_tmp.FC_Date, 3, 2) AS YYYYMM

FROM DSFM_Forecast_tmp LEFT OUTER JOIN

stock_master ON DSFM_Forecast_tmp.Stock_Code = stock_master.stock_code