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

  • have a look at this page

    http://www.sql-server-helper.com/tips/date-formats.aspx

    SELECT CONVERT(VARCHAR(10), GETDATE(), 131)


    Regards
    Satheesh


    jeudi 31 janvier 2013 15:16
    Auteur de réponse
  • if you are worried about how the date is stored in the tables, you cannot change the way how its stored.

    Whenever you retrieve, format it.

    have a look at thishttp://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server


    Regards
    Satheesh


    jeudi 31 janvier 2013 15:22
    Auteur de réponse
  • dear Satheesh Variath

    Thank you so much for your reply and help.  I executed the query above, but I sorry to say, I was unable to convert the date formats to dd/mm/yyyy in my sql server table.  

    Thank you for your response and time


    • Modifié missy786 jeudi 31 janvier 2013 15:25
    jeudi 31 janvier 2013 15:24
  • From your post you would like to UPDATE how the data is stored in the table.  Is the column  a DATE data type?  If it is then you are unable to modify the format of how it is stored.  If it is a character data type then you can update it.

    CREATE TABLE TABLENAMEHERE(
    YOURDATECOLUMN VARCHAR(10)
    );
    GO
    INSERT TABLENAMEHERE
    VALUES('01/01/2012'),
    	  ('02/01/2012'),
    	  ('03/01/2012'),
    	  ('04/01/2012');
    GO	  
    UPDATE TABLENAMEHERE
    SET YOURDATECOLUMN = 
    CONVERT(VARCHAR(10), CONVERT(DATE, YOURDATECOLUMN), 103) ;
    GO
    SELECT *
    FROM TABLENAMEHERE;
    GO
    DROP TABLE TABLENAMEHERE;
    GO


    David Dye My Blog

    jeudi 31 janvier 2013 15:25
    Modérateur
  • 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
  • You cannot change how it date is stored in the table, rather change how you display it using a convert while retrieving data.

    Another way is storing the data as varchar, but its not  recommended.

    click this link to know the internal structure of DATE & TIME

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server


    Regards
    Satheesh


    jeudi 31 janvier 2013 15:32
    Auteur de réponse
  • Assuming the data type is any of the date/time types in SQL Server, then the presentation from a SELECT is done by the client app. SQL Seerver returns binary data and the client formats into something human-readable. I suggest you use the client app to get the desired format, instead of forcing it in SQL Server (by converting it into a string in the SELECT that returns data). More info here: http://www.karaszi.com/SQLServer/info_datetime.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    jeudi 31 janvier 2013 18:41
  • 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
  • "when you declare a column data type to datetime, it stores the data in the default SQL server date format set on your instance."

    That part is not correct, I'm afraid. There is no server-side configurable datetime setting in SQL Server. There is a "default language" setting, but that doesn't affect storage fomat. If it did, just consider the consequences if you change the setting... There's info in http://www.karaszi.com/SQLServer/info_datetime.asp about what this setting does or does not affect.


    Tibor Karaszi, SQL Server MVP | web | blog

    lundi 4 février 2013 18:06
  • Hi Tibork,

    Absolutely correct. There is no SQL server side setting to change the date time format. The default date time format comes from the default timezone set on the windows server. Here is interesting example.

    http://www.high-flying.co.uk/sql/sql-default-date-format.html

    cheers

    kumar

    lundi 4 février 2013 22:22
  • Can you post the DDL for the table? Thanks.

    SQL Server 2012 datetime to string format:

    SELECT FORMAT (getdate(), 'dd/MM/yyyy');  -- 04/02/2013


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    mardi 5 février 2013 00:42
    Modérateur
  • "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