Solved

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now