none
How to change table's date format from yyyy/mm/dd to dd/mm/yyyy

    Question

  • Hi, 

    I would like to ask, if there is a way to convert date to dd/mm/yyyy format from yyyy/mm/dd in sql server table. 

    I tired executing the following statement but nothing is being changed int he actual table it self. 

    SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

    Any help regarding this issue, would be very much appreciated. 

    Thank you

    jeudi 31 janvier 2013 14:25

Réponses

  • hi

    Internally, values for all the date and time data types are stored completely differently from how you enter them or how they are displayed. Dates and times are always stored as two separate components: a date component and a time component.

    When SQL display the data it shows in yyyy/mm/dd hh:mm:ss format. 

    To display in different format  you should use CONVERT function. 

    another option is store the value as varchar. but this is not good practice. 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    jeudi 31 janvier 2013 15:26
  • Hi missy786,

    when you declare a column data type to datetime, it stores the data in the default SQL server date format set on your instance.

    No matter how it saves in the table you can control this through programming while reading and presenting.

    while reading from database in you scripts define date format

    SET DATEFORMAT DMY (OR) SET DATEFORMAT YMD

    while presenting it you can use convert finctions to display in the required format.

    here is the link to convert in to different formats.

    http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

    thanks

    kumar

    lundi 4 février 2013 04:26
  • "The default date time format comes from the default timezone set on the windows server."

    That is also a myth which is incorrect, I'm afraid. Datetime data is returned to the client as binary date. Not a string representation. There is no setting, anywhere, that will change this. It woulnd't be very user-friendly for a client app to show us a bunh of 0 and 1's, so a client application will, with a knowledge that this binary data represents datetime, format this to something that we humans will understand. Here the client app can respect the regional (locale) setting (not timezone) for the client OS, but whether it does depends solely on the person who wrote that client app. Try connecting SSMS 2005 to SQL Server 2008 and return date (not datetime), which the client app doesn't understand, and you will see a binary representation (since that version of tool doesn't understand the date type).


    Tibor Karaszi, SQL Server MVP | web | blog

    mardi 5 février 2013 06:46

Toutes les réponses