Home Forums SQL Server 2005 T-SQL (SS2K5) Convert MMDDYY to YYYYMMDD RE: Convert MMDDYY to YYYYMMDD

  • August 17, 2009 at 8:19 am

    #1040213

    conversions like this are a pain; obviously, whenever possible, this kind of data should be placed in a datatime filed, instead of a varchar/char; if you can, convert your original datasource to save you trouble in the future, but I know from experience that is not always possible.

    here's one way to do it:

    declare @notARealDate char(6)

    Set @notARealDate= '081709'

    --arbitrary cuttoff: anything that would be 51-99 is century 1900, else century 2000

    SELECT CASE

    WHEN CONVERT(INT,RIGHT(@notARealDate,2)) > 50

    THEN '19' + RIGHT(@notARealDate,2) + LEFT(@notARealDate,4)

    ELSE '20' + RIGHT(@notARealDate,2) + LEFT(@notARealDate,4)

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!