Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 44
Fixed Length SQL Query Question 3 28
Need help subtracting a value within my script 7 42
Help Extract Specific in SQL 8 27
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

24 Experts available now in Live!

Get 1:1 Help Now