CODEWITHSUNDEEP
c#asp.netsql-serverdatabasedatetime
Avia Afer
Avia Afer

Reputation: 866

DateTime Format set for the SQL Server or per database

Via this code:

var ciIL = CultureInfo.CreateSpecificCulture("he-IL");
var todayNow = DateTime.Now.ToString("g", ciIL );

the DateTime will generate in my local preferred format (04/07/2013 20:00)

And that is the way I set my windows localization format for date/time and same way I would really like it to be set, in SQL Server (currently I am using 2012 SP1)

So if it's too complicated to ask for totally configured SQL Server that way I will settle for an approach requires setting every new database.. so the setting would be per database

The problem is when I set the date in the dd/mm/yyyy format, and it gets translated by SQL, so in Management Studio it shows as 2013-04-07 20:00

But it gets returned to ASP.NET C# code as: 07/04/2013 20:00

I want the database to be as I need it to be without querying it using conversion methods

My collation setting in the current database is: Hebrew_100_CI_AS

and the server set to : Hebrew_100_CI_AI

Upvotes: 0

Views: 278

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500805

and returned to ASP.NET C# code as : 07/04/2013 20:00

No, it's returned to your .NET code as a DateTime. What you do with that is your business.

You need to differentiate between the intrinsic data (a date and time) and the textual representation which you can choose. Wherever possible, use the intrinsic data type, avoiding string conversions until you really need them. In particular, use parameterized SQL and keep the values as DateTime values in the parameters.

You may well be able to set how SQL Management Studio displays the data somewhere in the Settings, but fundamentally that won't affect your code.

Upvotes: 6

Related Questions