How to make SQL Server to save datetime with AM/PM format?

Question

I'm trying to make my SQL Server table datetime columns save datetime with AM/PM. How to make SQL Server to save datetime with AM/PM format?

Right now it saves date like this: 2012-01-23 14:47:00.000 Is it possible to save it 2012-01-23 02:47:00.000 PM ??

Or does SQL Server save the date and time in this format (2012-01-23 14:47:00.000) all the time and I need to convert it just on output and input?

Is it even possible to save it in this format (2012-01-23 02:47:00.000 PM)? Or does SQL Server save datetime in 24 hour format?

thanks indeed for any help. sorry for language. ;)

Solution 1

Internally the date and time are stored as a number.

Whether it's displayed in a 12 or 24 hour clock is up to the program formatting it for display.

Solution 2

As Andrew said, Datetime format is stored not as string. so, you can use CONVERT function to get the datetime value in approprate format. for example,

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

to learn more about datetime formatting, see this article

Solution 3

You can simply use CONVERT function as following:

select CONVERT(VARCHAR,GETDATE(),108)

http://www.fmsinc.com/free/NewTips/SQL/AM_PM_time_format_in_SQL.asp

http://msdn.microsoft.com/en-us/library/Aa226054

http://blogs.msdn.com/b/kathykam/archive/2006/09/29/773041.aspx

Solution 4

AM/PM serves only for visualization, if you need to display them, use CONVERT keyword:

SELECT CONVERT(varchar, YourDateTimeField, 109)
FROM YourTable

If you need to store AM/PM - it is makes no sense for datetime type, use varchar type instead.

View more solutions

Comments

[Mindaugas]

so you mean what in database the datetime field gonna look like this(2012-01-23 14:47:00.000) all the time. and its completely up to program settings how it will be displayed for end user?

[Oleg Dok]

@Mindaugas No, it will look like 35565.34536426, and other tools interpret this number as date and time

[Shaiju T]

for more on how insert to using sql query check this