Create Date from MMDDYYYY

Posted on 2008-11-05
Last Modified: 2009-04-27
I'm trying to find the best way to create a function to create a date in the format of MM/DD/YYYY from MMDDYYYY.  I've looked around but haven't been able to find anything that works.  I've tried convert() and a couple of other things but it doesn't recognize that format as a date.  I can't get the format that's provided changed.  

Thanks in advance for any help.
Question by:tim_cs
    LVL 92

    Accepted Solution

    SELECT SUBSTRING(SomeDate, 1, 2) + '/' + SUBSTRING(SomeDate, 3, 2) + '/' + SUBSTRING(SomeDate, 5, 4)
    LVL 16

    Expert Comment

    select to_char(to_date(mydate,'MMDDYYY'),'MM/DD/YYYY')
    LVL 8

    Expert Comment

    It depends.
    Where does your 'MMDDYYYY' come from? What datatype is it originally? And what are you trying to do? Is it for database input from some outside parameter? Or for ouput in your web application? Or processing inside database?
    LVL 15

    Author Comment

    matthewspatrick's suggestion seems to be working so far.  I'm just doing some more testing.  

    I'm importing data into a temp Table from a flat file.  Then processing the data to be stored in the proper DB tables.
    LVL 8

    Expert Comment

    I see. Then yes, it's a string, with all the string functions available to your disposal

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now