Post reply

simple function to convert char to date.

  • November 16, 2006 at 1:46 pm

    #67884

    Hi all,

    There is a column in my table which has a date value but the column is of varchar type. I need to export it to a text file in the format yyyymmdd. How can I do this?

    The column is now in mm/dd/yyyy format. Example - 7/1/2002. I need to get this as 20020701. Thanks!

  • November 16, 2006 at 2:45 pm

    #672469

    Strings usually can be implicitly converted to a datetime, but since your table does not have the correct data type it is possible that you have bad data.

    so...

    create table #test (pk int identity, mydatestring varchar(12))

    insert into #Test(mydatestring)

    values ('1/1/2006')

    insert into #Test(mydatestring)

    values ('12/21/2006')

    insert into #Test(mydatestring)

    values ('6/6/2006')

    -- NOt a valid date

    insert into #Test(mydatestring)

    values ('Foo')

    -- Results in an error

    select convert(varchar(12), convert(datetime, mydatestring), 112)

    from #Test

    -- Results

    Server: Msg 241, Level 16, State 1, Line 1

    Syntax error converting datetime from character string.

    -- Use this

    select convert(varchar(12), convert(datetime, mydatestring), 112)

    from #Test

    where isdate(mydatestring) > 0

Viewing 2 posts - 1 through 1 (of 1 total)

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