CODEWITHSUNDEEP
sql-serversql-server-2008datetime
Mindaugas
Mindaugas

Reputation: 163

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

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. ;)

Upvotes: 2

Views: 22250

Answers (5)

user2721607
user2721607

Reputation: 318

Depending on the accuracy of the datetime you are storing you might be able to clean it up with

REPLACE(CONVERT (varchar, YourDateTimeField, 109), ':00.0000000', ' ')

This will not work if your date field is populated with GETDATE() as that means it will contain seconds and milliseconds but it will work if the field is populated by a user and seconds and milliseconds are all zeros

Upvotes: 0

Infinity
Infinity

Reputation: 683

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

Upvotes: 3

Alex_L
Alex_L

Reputation: 2654

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

Upvotes: 5

Oleg Dok
Oleg Dok

Reputation: 21766

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.

Upvotes: 3

Andrew Kennan
Andrew Kennan

Reputation: 14157

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.

Upvotes: 8

Related Questions