Results 1 to 4 of 4

Thread: Date Conversion

  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/
    Last edited by r937; 09-29-03 at 22:58.

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •