Datetime conversion

  • Can I get some help in converting a column data formatted in (weekday,Month,date) Example:Monday, May 24th

    to date time2

    It gives me the following error:

    Conversion failed when converting date and/or time from character string.

    Thanks,

    Swetha K

  • Well, for starters, it's going to want a year.

    I don't have 2k8 handy to run a direct test for datetime2, but stripping off the 'day of week' at the beginning by grabbing everything after the first comma, then appending ', 2010' to the end of the result should fix the issue.

    IE: CAST( RIGHT( datefield, len(datefield) - charindex( ',', datefield)) + ', 2010' AS DATETIME) should work.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I tried,but it gives me the same error ..

    Please suggest.

    Swetha K

  • swethak13 (10/12/2010)


    I tried,but it gives me the same error ..

    Please suggest.

    Swetha K

    can you post details of the query you are using?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Get rid of the "th" after "24".

    select convert(datetime2,'May 24 2010') AS MyDate

    Results:

    MyDate

    ---------------------------

    2010-05-24 00:00:00.0000000

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

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