CODEWITHSUNDEEP
sql-server
Thomas
Thomas

Reputation: 34208

Sql server how to get current date as per setting

we know people set their date format like dd/MM/yyyy in their pc or may be MM/dd/yyyy. so i want to show date only according to system settings.

now i am doing this way

CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly;

if possible please help

Upvotes: 1

Views: 239

Answers (2)

Mark D
Mark D

Reputation: 5668

I don't think it can be done the way you are trying to..

If the user is querying using a query tool, then there are settings in that query tool on how to display dates.

If you have an application layer (web, client, etc) you need to format it according to the locale somewhere.

This can be

a) in SQL on the SQL-Server as follows:

DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result;
SELECT FORMAT ( @d, 'd', 'fr-FR' ) AS FR_Result;
SELECT FORMAT ( @d, 'd', 'de-DE' ) AS DE_Result;

(where the app passes the locale from the client through to the SQL)

b) in the app code .net/java using e.g. a Format class, etc.

public class FormatDate
{
   public static void Main()
   {
      DateTime dt = DateTime.Now;
      // Sets the CurrentCulture property to U.S. English.
      Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
      // Displays dt, formatted using the ShortDatePattern
      // and the CurrentThread.CurrentCulture.
      Console.WriteLine(dt.ToString("d"));

      // Creates a CultureInfo for German in Germany.
      CultureInfo ci = new CultureInfo("de-DE");
      // Displays dt, formatted using the ShortDatePattern
      // and the CultureInfo.
      Console.WriteLine(dt.ToString("d", ci));
   }
}

c) in the client e.g. if the user is looking at a web page you'll need to use e.g javascript as outlined here:

Display date/time in user's locale format and time offset

Upvotes: 3

gbn
gbn

Reputation: 432667

This can't be done in SQL Server. It doesn't know what a client's settings are.

You'd do this in the calling application or web server.

Upvotes: 3

Related Questions