Post reply

Conversion to Date

  • April 9, 2013 at 7:36 am

    #272798

    Hi everyone

    I have a field in one of my tables called FinancialMonth. It's a varchar field and it shows the financial month and year and displays them like 201210 ie January 2013 (month 10 of financial year 2012 = Jan 13). The first month in the new financial year would be 201301. Essentially I'd like to convert this field back to a valid SQL date. The day of the month can be the first day of the month.

    Thanks in advance.

    BO

  • April 9, 2013 at 7:44 am

    #1604808

    Something simple?

    DECLARE @Tmp TABLE (col1 varchar(8))

    INSERT INTO @Tmp

    SELECT '201210' UNION ALL

    SELECT '201211' UNION ALL

    SELECT '201212' UNION ALL

    SELECT '201301' UNION ALL

    SELECT '201202'

    SELECT CONVERT(date, col1 + '01', 101) FROM @Tmp

    (Date)

    2012-10-01

    2012-11-01

    2012-12-01

    2013-01-01

    2012-02-01

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 9, 2013 at 7:45 am

    #1604811

    Also, the date datatype won't work in SQL 2005, it's new to SQL 2008

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 9, 2013 at 8:05 am

    #1604829

    Perfect!

    Thanks for your speedy response....

    BO

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

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