Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: mmddyyyy

  1. #1
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    mmddyyyy

    A vendor is giving me date information in the mmddyyyy format. For example, 11291926 is November 29, 1926. I'm trying to convert it to datetime, but I can't find a code here that works:

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    another vendor gives me date information in the yyyymmdd format and I use 112 just fine, but this format is throwing me off. Any suggestions?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    You're just going to have to pre-convert the data.

    Or you could do it with a custom INSERT statement, of course. But there's no good way to bulk insert those values.

    If you really can't pass the data through some ASP or ASP.NET page on the way in--or some equivalent filter--then it might be best to insert it as-is into a temporary table and then run a query that puts it into the real table in the right format.

    Example:
    Code:
    INSERT INTO realTable ( datefield )
    SELECT CONVERT( DATETIME, LEFT(df,2) + '/' + SUBSTRING(df,3,2) + '/' + RIGHT(df,4), 101 ) 
    FROM tempTable

    By the way: Moderator, this belongs in the "Other database" forum.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    AustinV (03-10-2011)

  • #3
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    worked perfectly, thanks!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •