CODEWITHSUNDEEP
sql-serverdatetimeformat
DareDevil
DareDevil

Reputation: 5349

Converting a DateTime in 12Hour Format SQL Server

I have a scenario in which I have to format time in 12 Hours, if time enters in PM part, then I should show Time in 12 Hours Format with PM. below I have achieved the Format but I am unable to find how to extract 12 Hour format with current solution.

DECLARE @OpeningTime AS DATETIME
DECLARE @ClosingTime AS DATETIME

SET @OpeningTime = GETDATE()
SET @ClosingTime = '2015-12-29 13:52:36'

SELECT  '<StoreTimings><Section><Days>'
                    + LEFT(DATENAME(WEEKDAY, GETDATE()), 3)
                    + '</Days><Timing>'
                    + CONVERT(VARCHAR(5), @OpeningTime, 108)
                    + ( CASE WHEN DATEPART(HOUR,@OpeningTime) > 12
                             THEN ' PM'
                             ELSE ' AM'
                        END ) + '-'
                    + CONVERT(VARCHAR(5), @ClosingTime, 108)
                    + ( CASE WHEN DATEPART(HOUR, @ClosingTime) > 12
                             THEN ' PM'
                             ELSE ' AM'
                        END ) + '</Timing></Section></StoreTimings>'

The Output is as below

<StoreTimings><Section><Days>Tue</Days><Timing>10:31 AM-13:52 PM</Timing></Section></StoreTimings>

I need to show 01:52 PM rather 13:52 PM

Upvotes: 2

Views: 1175

Answers (2)

DareDevil
DareDevil

Reputation: 5349

I have solved my issue by following script.

DECLARE @OpeningTime AS DATETIME
DECLARE @ClosingTime AS DATETIME

SET @OpeningTime = '2015-12-30 00:30:36'
--GETDATE()
SET @ClosingTime = '2015-12-30 23:59:36'



DECLARE @StoreOpeningTime AS NVARCHAR(8)
DECLARE @StoreClosingTime AS NVARCHAR(8)
DECLARE @Hours AS INT 
SET @Hours = 12

SELECT  @StoreOpeningTime = CASE WHEN DATEPART(HOUR, @OpeningTime) >= 12
                             THEN RIGHT('0'
                                        + ( CONVERT(VARCHAR(2), DATEPART(HOUR,
                                                          @OpeningTime)
                                            - @Hours) ), 2) + ':'
                                  + RIGHT('0'
                                          + ( CONVERT(VARCHAR(2), DATEPART(MINUTE,
                                                          @OpeningTime)) ),
                                          2)
                             ELSE CONVERT(VARCHAR(5), @OpeningTime, 108)
                        END
    + ( CASE WHEN DATEPART(HOUR, @OpeningTime) >= 12 THEN ' PM'
             ELSE ' AM'
        END )


SELECT  @StoreClosingTime = CASE WHEN DATEPART(HOUR, @ClosingTime) >= 12
                             THEN RIGHT('0'
                                        + ( CONVERT(VARCHAR(2), DATEPART(HOUR,
                                                          @ClosingTime)
                                            - @Hours) ), 2) + ':'
                                  + RIGHT('0'
                                          + ( CONVERT(VARCHAR(2), DATEPART(MINUTE,
                                                          @ClosingTime)) ),
                                          2)
                             ELSE CONVERT(VARCHAR(5), @ClosingTime, 108)
                        END
    + ( CASE WHEN DATEPART(HOUR, @ClosingTime) >= 12 THEN ' PM'
             ELSE ' AM'
        END )


SELECT  '<StoreTimings><Section><Days>' + LEFT(DATENAME(WEEKDAY, GETDATE()), 3)
    + '</Days><Timing>' + @StoreOpeningTime + '-' + @StoreClosingTime
    + '</Timing></Section></StoreTimings>'

Demo at this Link FIDDLE DEMO

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

You can simply do it like this:

DECLARE @OpeningTime AS DATETIME
DECLARE @ClosingTime AS DATETIME

SET @OpeningTime = GETDATE()
SET @ClosingTime = '2015-12-29 13:52:36'

SELECT CONVERT(VARCHAR, @OpeningTime, 100)
SELECT CONVERT(VARCHAR, @ClosingTime, 100)

FIDDLE DEMO

Also check the MSDN list for CONVERT option.

EDIT:

To add a space between AM/PM and the time part. You can do

select convert(varchar(10), @ClosingTime,120) + RIGHT(CONVERT(CHAR(20), @ClosingTime, 22), 11)

FIDDLE DEMO

EDIT:

To get the date in AM/PM format with a space and in HH:MM AM/PM format

select convert(varchar(10), @ClosingTime,120) + ' ' + REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getdate(),100),7)),7),'AM',' AM'),'PM',' PM')

FIDDLE DEMO

Upvotes: 1

Related Questions