Convert Time DataType into AM PM Format

Question

I have one Table which has two fields such as  StartTime  and  EndTime   The DataType of the Two columns are Time   So the Values of the Table looks like as follows   TableA               StartTime                EndTime        ------------------         ----------------         17 30 00 0000000          17 57 00 0000000   But I need the result as               StartTime                EndTime        ------------------         ----------------             05 30 PM                 05 57 PM   When I select the table  How to get time in AM PM Format

User · Answer

Using  Saikh s answer above  the 2nd option  you can add a space between the time itself and the AM or PM   REVERSE LEFT REVERSE CONVERT VARCHAR 20  CONVERT TIME myDateTime  100   2          SUBSTRING REVERSE CONVERT VARCHAR 20  CONVERT TIME myDateTime  100   3 20   AS  Time     Messy I know  but it s the solution I chose  Strange that the CONVERT   doesn t add that space automatically  SQL Server 2008 R2

User · Answer

Use following syntax to convert a time to AM PM format   Replace the field name with the value in following query   select CONVERT varchar 15  CAST  17 30 00 0000000  AS TIME  100

User · Answer

gt  SELECT CONVERT VARCHAR 30   GETDATE    100  as date n time  gt  SELECT CONVERT VARCHAR 20  convert time GETDATE    100  as req time  gt  select convert varchar 20  GETDATE   103      convert varchar 20  convert time getdate    100    gt  Result  1  - Jun  9 2018 11 36AM  gt  result 2  -  11 35AM  gt  Result  3  -  06 10 2018 11 22AM

User · Answer

In SQL 2012 you can use the Format   function   https   technet microsoft com en-us library hh213505 28v sql 110 29 aspx  Skip casting if the column type is  datetime    Example   SELECT FORMAT StartTime  hh mm tt   AS StartTime FROM TableA

User · Answer

select CONVERT varchar 15  CAST  17 30 00 0000000  AS TIME  100    almost works perfectly except for the space issue  if that were changed to    select CONVERT varchar 15  CAST  17 30 00 0000000  AS TIME  22       then you get the space  And additionally  if the column being converted is already of TIME format  you can skip the cast if you reference it directly   Final answer    select CONVERT varchar 15  StartTime 22

User · Answer

Try this   select CONVERT varchar 15  CAST  2014-05-28 16 07 54 647  AS TIME  100  as CreatedTime

User · Answer

Try this   select CONVERT VARCHAR 5     4 07PM   108          RIGHT CONVERT VARCHAR 30     4 07PM   9  2  as ConvertedTime

User · Answer

SELECT CONVERT varchar  StartTime  100  AS ST         CONVERT varchar  EndTime  100  AS ET FROM some table   or  SELECT RIGHT  0   LTRIM RIGHT CONVERT varchar  StartTime  100  8   8  AS ST         RIGHT  0   LTRIM RIGHT CONVERT varchar  EndTime  100  8   8  AS ET FROM some table

User · Answer

Multiple functions  but this will give you what you need  tested on SQL Server 2008   Edit  The following works not only for a time type  but for a datetime as well   SELECT SUBSTRING CONVERT varchar 20  StartTime 22   10  11  AS Start  SUBSTRING CONVERT varchar 20  EndTime 22   10  11  AS End FROM  TableA

User · Answer

select right convert varchar 20  getdate   100  7

User · Answer

Here are the various ways you may pull this  depending on your needs    Using the Time DataType   DECLARE  Time Time    15 04 46 217  SELECT -- 3 04PM         CONVERT VarChar 7    Time  0           --  3 04PM  --Leading Space         RIGHT       CONVERT VarChar 7    Time  0   7           --  3 04 PM  --Space before AM PM         STUFF RIGHT       CONVERT VarChar 7    Time  0   7   6  0                -- 03 04 PM  --Leading Zero   This answers the question above         STUFF RIGHT  0    CONVERT VarChar 7    Time  0   7   6  0               -- 03 04 PM  --This only works in SQL Server 2012 and above              FORMAT CAST  Time as DateTime    hh mm tt  --Comment out for SS08 or less    Using the DateTime DataType   DECLARE  Date DateTime    2016-03-17 15 04 46 217  SELECT --  3 04PM  --No space before AM PM         RIGHT CONVERT VarChar 19    Date  0   7           --  3 04 PM  --Space before AM PM         STUFF RIGHT CONVERT VarChar 19    Date  0   7   6  0                -- 3 04 PM  --No Leading Space         LTRIM STUFF RIGHT CONVERT VarChar 19    Date  0   7   6  0                 -- 03 04 PM  --Leading Zero         STUFF REPLACE RIGHT CONVERT VarChar 19    Date  0   7         0    6  0               -- 03 04 PM  --This only works in SQL Server 2012 and above              FORMAT  Date   hh mm tt  --Comment line out for SS08 or less

User · Answer

This returns like 11 30 AM  select CONVERT VARCHAR 5   FromTime  108          RIGHT CONVERT VARCHAR 30   FromTime  9  2  from tablename

[sql-server] Convert Time DataType into AM PM Format:

Examples related to sql-server

Examples related to sql-server-2008

Examples related to select

Examples related to time