CODEWITHSUNDEEP
c#sql-server
Rod
Rod

Reputation: 15423

time format on my sql server

my c# program selects a datetime field from database and returns the data like the following:

21/06/2012 4:11:48 p.m.

it is not stored this way though (it's stored like 2012/06/21 15:19:10:000). my locale is set to English/New Zealand

but when I try to save it back to database

the ISDATE() sql function doesn't like that format and just returns NULL. Do I have any options to set the time format in sql or c#? What in sql or c# is making it put "p.m."? (instead of just "PM", which by the way works)

Summing up the problem

See latest related link

Upvotes: 0

Views: 1152

Answers (4)

InspiredBy
InspiredBy

Reputation: 4320

Most of the SQL datetime formats should be recognizable by C# DateTime.Parse(youSQLDatetimeVariable). I find it a bit odd what's happening in your case.

Like other said you really shouldn't but here is how you can convert DateTime to various formats:

SELECT Convert(VARCHAR, MyDateTimeField, 112) from MyTable

That number (112 ) indicates the DateTime format that will appear as Varchar All formats can be found here in details: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Or within your C# code with 

YourDatetimeVariable.ToString("MM-dd-yyyy")

Or whatever format you want within those quotes where MM- month number, dd - day number, yyyy - year.

Upvotes: 1

NX1
NX1

Reputation: 68

Note: See Jon's response about properly saving the DateTime in the database rather than a string.

For C# String formatting:

The DateTime object can output in different formats using the .ToString() function.

Please see the following links

So, for example:

DateTime.Now.ToString("dd/MM/yyyy h:mm:ss");

That will output in the format you have in your question.

Upvotes: 2

Christopher Rathermel
Christopher Rathermel

Reputation: 935

I think your issue is related to the Set language setting in sql.

Similar to this question: SQL Server ISDATE() Function - Can someone explain this?

Can you try to set your sql language to match your format?

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499800

You shouldn't be saving it in the database as text at all - you should be using the relevant data type such as DATETIME, and passing DateTime values from .NET to the database using SQL parameters. If you're using the right data type, the value won't actually be stored as "2012/06/21 15:19:10:000" at all. That may be how you see it in SQL Server Studio or something similar, but that doesn't mean it's the raw storage format.

Logically, a date/time doesn't have a format any more than the number ten is "10" (decimal) vs "A" (hex). Avoid string conversions as far as you can, and your life will be easier.

Upvotes: 3

Related Questions