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

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.

DECLARE
      @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
schwadAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SheilsConnect With a Mentor Commented:
My understanding is that mysql stores time in the 24hr format but you can format the form to display in am/pm
0
 
David L. HansenProgrammer AnalystCommented:
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
0
 
David L. HansenConnect With a Mentor Programmer AnalystCommented:
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.
0
 
bombwriterCommented:
Take a look to DATE_FORMAT function
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

DATE_FORMAT(DATE, "FORMAT");

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

0
 
cyberkiwiConnect With a Mentor Commented:
> 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)

e.g.

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.