Post reply

convert from MMMDDYYYY to DATETIME

  • February 26, 2014 at 3:07 pm

    #284017

    how do i convert DEC312013 to '2013-12-31 00:00:00.000' without using CASE in sql.

    i need to replace the following code in my proc with new logic because my SQL SME wont let me hardcode the months for somereason and i am using 2005.

    DECLARE @date1 varchar(10)

    SET @DATE1 = 'DEC312013'

    SELECT (CONVERT(DATETIME, (CASE SUBSTRING(@date1,

    1, 3)

    WHEN 'JAN'

    THEN '1'

    WHEN 'FEB'

    THEN '2'

    WHEN 'MAR'

    THEN '3'

    WHEN 'APR'

    THEN '4'

    WHEN 'MAY'

    THEN '5'

    WHEN 'JUN'

    THEN '6'

    WHEN 'JUL'

    THEN '7'

    WHEN 'AUG'

    THEN '8'

    WHEN 'SEP'

    THEN '9'

    WHEN 'OCT'

    THEN '10'

    WHEN 'NOV'

    THEN '11'

    WHEN 'DEC'

    THEN '12'

    END) + '/'

    + SUBSTRING(@date1, 4, 2)

    + '/' + SUBSTRING(@date1, 6,4)))

  • February 26, 2014 at 3:44 pm

    #1692462

    Use the STUFF function to format it into a string that can be converted into a date using the normal convert function. Once it's a real date datetype, you can use convert to format it however you'd like.

    DECLARE @date1 varchar(12)

    DECLARE @date2 date

    SET @DATE1 = 'DEC312013'

    select @date1 = STUFF(stuff(@date1,4,0,' '),7,0,' ')

    select @date2 = CONVERT(date,@date1,100)

    select @date2

    And then again, I might be wrong ...
    David Webb

  • February 27, 2014 at 7:44 am

    #1692691

    Thank you:-)

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

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