locked
convert RRS feed

  • Question

  • hi,
    How do I convert this into smalldatetime please?
    I am doing this because there is a field of type varchar which has to go into a separate table with field of type smalldatetime.

    select convert(smalldatetime, '14/10/04', 101)

    This is what I have but the error is:
    Conversion failed when converting character string to smalldatetime data type.

    Wednesday, July 18, 2007 4:04 PM

Answers

  • This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert.  You might also want to give a look to Umachandar's comments in this post:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

     

     Note the suggestion to try to use one of the ISO formats when possible.

     

    Code Snippet

    set dateformat dmy

    select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

     

    /*
    a date/time                   
    ------------------------------
    10/14/2007
    */

     

    select cast('14/10/7' as smalldatetime) as [converted]

     

    /*
    converted                                             
    ------------------------------------------------------
    2007-10-14 00:00:00
    */

     

     

    Wednesday, July 18, 2007 4:20 PM

All replies

  • This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert.  You might also want to give a look to Umachandar's comments in this post:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

     

     Note the suggestion to try to use one of the ISO formats when possible.

     

    Code Snippet

    set dateformat dmy

    select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

     

    /*
    a date/time                   
    ------------------------------
    10/14/2007
    */

     

    select cast('14/10/7' as smalldatetime) as [converted]

     

    /*
    converted                                             
    ------------------------------------------------------
    2007-10-14 00:00:00
    */

     

     

    Wednesday, July 18, 2007 4:20 PM
  • Try this:

     

    select convert(smalldatetime, '10/14/2004', 101)

     

    You already had smalldatetime and the system was probably expecting the MM/DD/YYYY format rather than DD/MM/YYYY

     

     

    Wednesday, July 18, 2007 4:22 PM