'How to specify a 12-hour time format in SQL Server 2008

I am using SQL Server 2008 and I have a database file (mydata.mdf) in which I have a column in one of the table and that column has the datatype (Time) .

I added this database file to my WPF-C# project (using VS2010) , but I had a problem and its as follows :

This (Time) column treats time in 24-Hours system , but I want to use (12-Hour) system in my application , so is there a way to define a 12-Hour system time in SQL server2008 .

and if there isn't , what do you think is the best way to handle that ???

Pleeeeeeeeeeease help me ASAP because I'm in a hurry and I can't figure it out ...

c++sharpsqlwpfdatabasedatetime-format


Solution 1:[1]

The answer is you can't really, but don't worry, it's not a problem. Format the date in your C# code.

The point is that a date and time is an absolute value, which is what you want SQL to store, then 12 hour vs 24 hour clock is merely a display detail, eg, 13:00 and 1:00pm are equivalent, don't worry about how SQL stores it, then in C# use the following to display it:

DateTime myDateTime = GetTheTimeFromSomeMethod();
myDateTime.ToString("h:mm:ss tt");

There are lots of guides, This is a good one, but there are plenty of others eg this one

Solution 2:[2]

Does it have to be formatted from the database? C# and WPF both provide many built-in date format options. For example, check out the ContentStringFormat property on a Label.

If you must do it in the database, here is a messy workaround which will work

It formats the date as a string using a 12h clock, then removes the date part of it

select right(convert(varchar, cast('1/1/2010 23:59:59' as datetime), 100), 
charindex(' ', reverse(convert(varchar, cast('1/1/2010 23:59:59' as datetime), 100)))-1)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Glorfindel
Solution 2 Rachel

Related Questions