Solved

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

Posted on 2010-08-19
5
2,969 Views
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.

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
0
Comment
Question by:schwad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
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
0
 
LVL 16

Accepted Solution

by:
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
0
 
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.
0
 
LVL 3

Expert Comment

by:bombwriter
ID: 33484508
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
 
LVL 58

Assisted Solution

by:cyberkiwi
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)

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

763 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