Date Conversion

Hi,

Please help me on this conversion. I am using DTS to import data from text file to SQL Server 2000. I have these Date fields in the textfile

Date1 yyyymmdd
Date2 yyyymm

What corresponding data type should I define in SQL server. Datetime and smalldate does not work.

ThanksHowdy

The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.

If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'

Date format in BOL is not documented well, sadly.

So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.

Let me know if I have interpreted your problem correctly.

Cheers,

SG.|||i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)

once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table

e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say

select
cast( left(fieldx,4)
+'-'+substring(fieldx,5,2)
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-'+substring(fieldy,5,2)
+'-01' as datetime ) as fieldydate
, ...
into newtable
from loadedtable

edit: cut & paste typo
rudy
http://r937.com/|||the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.

Hope this helps.