SQL Server Performance

Date Convert Issue

Discussion in 'Getting Started' started by AJITH123, Jul 18, 2007.

  1. AJITH123 Member

    Hi,

    I am having a table with one date field [DOJ] which has a mixed format format, that is some of the records are in mm/dd/yy and some are dd/mm/yy. I need to convert the entire date [DOJ] to dd/mm/yy format.

    Can any one give the idea for how to do it ?

    Also,

    If the date is stored in dd/mm/yy how can I convert the same in to MM/DD/YY format
    i tried [Select convert(datetime,Todate,101) from Temp_Table], Temp_Table having data in dd/mm/yy format but failed, since it is showing out-of-range datetime value.

    Thanks
    Ajith
  2. dineshasanka Moderator

    I have a question, in your data dates like 1/7/2007 is it July 1st or January 7th.



    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. AJITH123 Member

    The fomat is like 1/7/2007 [dd/mm/yyyy]

  4. AJITH123 Member

    I resolved the issue by using the 'SET DATEFORMAT dmy;' command

    Please tell whether any other solution for resolving the first point ??
  5. Madhivanan Moderator


    1 Always use proper DATETIME datatype
    2 When querying always use ISO format YYYY-MM-DD
    3 Let front end do the convertion

    Madhivanan

    Failing to plan is Planning to fail
  6. dineshasanka Moderator

    you can convert function as well

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  7. AJITH123 Member

    The data whatt I spoke is already in the data base...is a legacy system, so i canot do it in front end like 'Madhivanan' said...I am expecting a solution for the issue...not an advice..!!
  8. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by AJITH123</i><br /><br />The data whatt I spoke is already in the data base...is a legacy system, so i canot do it in front end like 'Madhivanan' said...I am expecting a solution for the issue...not an advice..!!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">If the datatype is not DATETIME then there is no solution except advice [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page