Post reply

CONVERT STRING INTO DATETIME

  • July 20, 2009 at 1:27 pm

    #134448

    I have a ssis package to load from flatfile to sql tbl.

    On my flatfile last column (Ragged right flat file) , which is a 6 length char, represents a date data.

    The data looks like this

    090715

    090824

    .............

    My sql tbl column is smalldatetime.........

    I tried all data conversations options as well as derived column transform. But none of them seems to work.

    Can someone help me what is the right conversions or what is the way to convert this to datetime?

  • July 20, 2009 at 1:37 pm

    #1027265

    Those character strings will CAST or CONVERT directly into smalldatetime, or even implicitly convert.

    select CAST('090715' as smalldatetime)

    -- implicit conversion

    declare @date as smalldatetime

    set @date = '090715'

    select @date as [@date]

    Can you show us how you are trying to do it?

    When you say "none of them seem to work", what results or errors are you getting?

    It's the last column. Is there a possibility that there is an extra control character that you are including in the string? Perhaps taking a left (datecol,6) would get rid of that.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 20, 2009 at 1:44 pm

    #1027268

    I am using SSIS to load. Inside my dataflow i have a source and destination. SOurce is my flat file and destination is my SQL tbl.

    THis is my data from flat file which represents 3 columns. the last column is date which i need to insernt into sql table that is datetime

    00092SP1090717

    00460SP1090717

    00462SP1090717

    00680SP1090717

    00726SP1090717

    00730SP1090717

    01768SP1090717

    01878SP1090717

    01921SP1090717

    01931SP1090717

    02075SP1090717

  • July 20, 2009 at 5:17 pm

    #1027355

    Your not really asking for a T-SQL answer then your looking for SSIS answer which is in a different section of the SQL 2005 forums.

    For T-SQL (lets say you brought the column in as a varchar first then dumped it to a second table after the import) you could use the cast as mentioned above

    SELECT CAST('090717' AS SMALLDATETIME)

    Results

    2009-07-17 00:00:00

  • July 20, 2009 at 5:56 pm

    #1027370

    Sorry Guys looks liek I posted in wrong section..........It should be in SSIS.............section.......my whole day was wrong!!!!

Viewing 5 posts - 1 through 4 (of 4 total)

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