locked
Date table with Australian date format RRS feed

  • Question

  • I'm using SQL Server 2012, I want to create a date table and I need the format for be dd/mm/yyyy.

    for some reason no matter what I do, the results in the SQL Server do not show this format. I tired to use CONVERT().

    I even created an excel spreadsheet with some dates in the format I want and tried to import into my database. When I look at the preview, it's all correct

    But then when I run the query is switches it back

    My date in the Windows is in the correct format.

    Any ideas how to fix this?

    Thank you

    • Moved by Tom Phillips Wednesday, June 26, 2013 1:45 PM TSQL question
    Friday, June 21, 2013 1:21 AM

Answers

  • for some reason no matter what I do, the results in the SQL Server do not show this format.


    SQL Server saves the date in a native (numeric) format. To "show" the value is part of the frontend, so your frontend shows it in a wrong way. And if it's SSMS 2012, it always shows date value in ISO format.

    Olaf Helper

    Blog Xing

    • Proposed as answer by TiborKMVP Friday, June 21, 2013 2:42 PM
    • Marked as answer by toby_m31 Friday, June 28, 2013 1:06 AM
    Friday, June 21, 2013 10:18 AM

All replies

  • try below query,

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


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Fanny Liu Saturday, June 22, 2013 7:43 AM
    Friday, June 21, 2013 2:28 AM
  • Are you trying to link/import through a ODBC DSN (Data Source Name)?

    In the configuration of the DSN, there exists the option "Use  Reginal Settings when outputting currency, numbers, dates and times".  If you select this option, the output should be in the format you want.

    Alternatively, you can simply set the required format in your Excel cell(s).  After all, the formatting is the function of the client application.


    Van Dinh

    Friday, June 21, 2013 10:02 AM
  • for some reason no matter what I do, the results in the SQL Server do not show this format.


    SQL Server saves the date in a native (numeric) format. To "show" the value is part of the frontend, so your frontend shows it in a wrong way. And if it's SSMS 2012, it always shows date value in ISO format.

    Olaf Helper

    Blog Xing

    • Proposed as answer by TiborKMVP Friday, June 21, 2013 2:42 PM
    • Marked as answer by toby_m31 Friday, June 28, 2013 1:06 AM
    Friday, June 21, 2013 10:18 AM
  • thanks I now realise that this is the issue.
    Friday, June 28, 2013 1:05 AM