none
SQL Server 2000 string to datetime conversion

    Question

  • Good grief SQL Server world - I have never seen so much fuss over what should be very simple.

    How do I convert this string 09012011 to datetime 2011-09-01 00:00:00

    There are many articles - no working solutions yet.  Help appreciated - but I don't want to do pages of code to convert one column.  Isn't there a simple format command in SQL Server?


    Dianne
    Tuesday, October 25, 2011 3:58 PM

Answers

  • Hi, try please :

    select cast (Right('09012011',4)+Left('09012011',2)+substring('09012011',3,2) as datetime)

     

     

    Best regards
    • Proposed as answer by Naomi NModerator Tuesday, October 25, 2011 4:27 PM
    • Edited by Badii Gharbi Tuesday, October 25, 2011 4:29 PM
    • Marked as answer by DeeScottie Tuesday, October 25, 2011 6:18 PM
    Tuesday, October 25, 2011 4:06 PM

All replies

  • Hi, try please :

    select cast (Right('09012011',4)+Left('09012011',2)+substring('09012011',3,2) as datetime)

     

     

    Best regards
    • Proposed as answer by Naomi NModerator Tuesday, October 25, 2011 4:27 PM
    • Edited by Badii Gharbi Tuesday, October 25, 2011 4:29 PM
    • Marked as answer by DeeScottie Tuesday, October 25, 2011 6:18 PM
    Tuesday, October 25, 2011 4:06 PM
  • See if this helps. It might need a little finessing depending on the language settings of your connection to SQL as to whether the character string is interpreted as ddmmyyyy or mmddyyyy.

    SELECT

     

    Cast(LEFT(Field1, 2) + '/' + SUBSTRING(Field1, 3, 2) + '/' + RIGHT(Field1, 4) AS DateTime) AS

    DateField

    FROM

     

    (Select '09012011' AS Field1) AS

    X

     
    Tim Johnstone Senior Technical Consultant Computacenter (UK) Ltd
    • Proposed as answer by Tim Johnstone Tuesday, October 25, 2011 4:33 PM
    Tuesday, October 25, 2011 4:09 PM
  • Formatting went a bit wild there - sorry about that!
    Tim Johnstone Senior Technical Consultant Computacenter (UK) Ltd
    Tuesday, October 25, 2011 4:09 PM
  • Hi  Dianne !
     
    Lets try this out;

    SELECT CAST(SUBSTRING('09012011',5,4) + SUBSTRING('09012011',1,2) + SUBSTRING('09012011',3,2) AS DATE)
    


    Note : We have DateTime / Date Data Types in MS SQL. Avoid using VARCHAR for Storing Dates.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

     

    Tuesday, October 25, 2011 4:24 PM
    Answerer
  • Sorry, as stated this is sql 2000 - date doesn't appear until 2008
    Dianne
    Tuesday, October 25, 2011 6:17 PM
  • Excellent!  Thank you very kindly.
    Dianne
    Tuesday, October 25, 2011 6:19 PM