Sql query returns 24 hour time format instead of 12 hour am/pm format:

Posted on 2010-08-19
Last Modified: 2013-11-05
The following query returns the datetime fields as a 24 hour format but I desire a 12 hour AM/PM format.  Note:  The tables referenced in the following query have all datetime fields in the 12 hour format.

      @CurrentDate AS nvarchar(20),
      @Today AS datetime

SET @CurrentDate = CONVERT(NVARCHAR(20), GETDATE(), 111)
SET @CurrentDate = @CurrentDate + ' 12:00 AM'
SET @Today = CAST(@CurrentDate AS DATETIME)

print '@CurrentDate = ' + @CurrentDate
print '@Today = ' + CAST(@Today AS VARCHAR(20))

SELECT     TOP(100) dbo.T_Schedule.SCH_Date, dbo.T_Model.MDL_Number, dbo.T_ScheduleSequence.SCHSEQ_ProcessID,
                      dbo.T_ScheduleSequenceHistory.SSH_Status, dbo.T_ScheduleSequenceHistory.SSH_CreateDate
FROM         dbo.T_ScheduleSequenceHistory INNER JOIN
                      dbo.T_ScheduleSequence ON dbo.T_ScheduleSequenceHistory.SSH_SCHSEQID = dbo.T_ScheduleSequence.SCHSEQ_ID INNER JOIN
                      dbo.T_Model ON dbo.T_ScheduleSequence.SCHSEQ_MDLID = dbo.T_Model.MDL_ID INNER JOIN
                      dbo.T_Schedule ON dbo.T_ScheduleSequence.SCHSEQ_SCHID = dbo.T_Schedule.SCH_ID
WHERE     (dbo.T_Schedule.SCH_LID = 3) AND (dbo.T_ScheduleSequenceHistory.SSH_CreateDate >= @CurrentDate)
ORDER BY dbo.T_ScheduleSequenceHistory.SSH_CreateDate
Question by:schwad
LVL 15

Expert Comment

by:David L. Hansen
ID: 33478744
Select Ltrim(right(convert(char(19),getdate(),100),7))-- will return this: 4:10pm

Select Ltrim(right(convert(char(26),getdate(),109),14)) -- will return this: 4:19:18:810pm
LVL 16

Accepted Solution

Sheils earned 167 total points
ID: 33478819
My understanding is that mysql stores time in the 24hr format but you can format the form to display in am/pm
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 167 total points
ID: 33478852
mysql?  Are you using SQL Server or something else?

Yes, almost any database will always store times in a 24hr. format (or universal format).  Getting it to look the way you want can be done by the query or by the application at runtime.

Expert Comment

ID: 33484508
Take a look to DATE_FORMAT function


The modifiers for format the date are
Specifier 	Description
%a 	Abbreviated weekday name (Sun..Sat)
%b 	Abbreviated month name (Jan..Dec)
%c 	Month, numeric (0..12)
%D 	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d 	Day of the month, numeric (00..31)
%e 	Day of the month, numeric (0..31)
%f 	Microseconds (000000..999999)
%H 	Hour (00..23)
%h 	Hour (01..12)
%I 	Hour (01..12)
%i 	Minutes, numeric (00..59)
%j 	Day of year (001..366)
%k 	Hour (0..23)
%l 	Hour (1..12)
%M 	Month name (January..December)
%m 	Month, numeric (00..12)
%p 	AM or PM
%r 	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S 	Seconds (00..59)
%s 	Seconds (00..59)
%T 	Time, 24-hour (hh:mm:ss)
%U 	Week (00..53), where Sunday is the first day of the week
%u 	Week (00..53), where Monday is the first day of the week
%V 	Week (01..53), where Sunday is the first day of the week; used with %X
%v 	Week (01..53), where Monday is the first day of the week; used with %x
%W 	Weekday name (Sunday..Saturday)
%w 	Day of the week (0=Sunday..6=Saturday)
%X 	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x 	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y 	Year, numeric, four digits
%y 	Year, numeric (two digits)
%% 	A literal “%” character
%x 	x, for any “x” not listed above

Open in new window

LVL 58

Assisted Solution

cyberkiwi earned 166 total points
ID: 33489720
> The following query returns the datetime fields as a 24 hour format but I desire a 12 hour AM/PM format.

The query returns datetime fields as some internal floating point number that is up to the FRONT END programming to format.
If you are using SSMS to run queries, it displays datetime values in the default 24-hr format.
If you are only using SSMS, you can convert to to YYYY-MM-DD HH:MM:SS.sssAM using

convert(char(11), dateColumn, 120) + right(convert(varchar(30),dateColumn,109),14)


convert(char(11), dbo.T_ScheduleSequenceHistory.SSH_CreateDate, 120) + right(convert(varchar(30),dbo.T_ScheduleSequenceHistory.SSH_CreateDate,109),14)

If you use that in a query for some front end programming, it will become a VARCHAR column and will not be suitable for datetime operations.

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question