How to modify code to display '21:30:00 PM' as '09:30:00 PM'?

Question

How to modify code to display '21:30:00 PM' as '09:30:00 PM'?

adonetok

SSCrazy Eights

Points: 8395
More actions
November 7, 2011 at 10:12 am

#250376

The code below will display as '21:30:00 PM'
How to modify code to display as '09:30:00 PM'?
declare @date datetime
set @date = '2011-11-08 21:30:00.000'
select CONVERT(VARCHAR, @date, 108) + ' ' + Substring(CONVERT(VARCHAR, @date, 100), 18, 2)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

adonetok (11/7/2011)
The code below will display as '21:30:00 PM'
How to modify code to display as '09:30:00 PM'?
declare @date datetime
set @date = '2011-11-08 21:30:00.000'
select CONVERT(VARCHAR, @date, 108) + ' ' + Substring(CONVERT(VARCHAR, @date, 100), 18, 2)

Here

SELECT RIGHT('0'+ LTRIM(SUBSTRING(REPLACE(REPLACE(':'+CONVERT(VARCHAR(26),@date,9),CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'AM',' AM'),':'+CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'PM',' PM'),13,26)),22)

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(N), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b(N),

(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) c(N)

WHERE CHARINDEX(' ',LTRIM(SUBSTRING(REPLACE(REPLACE(':'+CONVERT(VARCHAR(26),@date,9),CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'AM',' AM'),':'+CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'PM',' PM'),13,26))) > 1

Or here

SELECT STUFF(REPLACE(STUFF(CONVERT(char(26),@date,109),1,12,''),' ','0'),9,4,' ')

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

This

declare @date datetime

set @date = '2011-11-08 21:30:00.000'

select CONVERT(VARCHAR(30), @date, 109)

Or this

declare @date datetime

set @date = '2011-11-08 21:30:00.000'

select CONVERT(VARCHAR(30), @date, 100)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

try this...hopefully it meets your specific requirements:

declare @date datetime

set @date = '2011-11-08 21:30:00.000'

select convert(varchar,@date, 103) --- change 103 to 101 if mm/dd required

+ ' '

+ CONVERT(VARCHAR(30), Dateadd(HH, CASE WHEN Datepart(HH, @date)> 12 THEN -12 ELSE 0 END, @date), 108)

+ ' '

+ CASE WHEN Datepart(HOUR, @date) > 12 THEN 'PM' ELSE 'AM' END

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

adonetok SSCrazy Eights Points: 8395 More actions · Answer 4

Thank all of you for help

I use J Livingston SQL's code, it works great!

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

Here is another way for grins:declare @date datetime

set @date = '2011-11-08 21:30:00.000'

SELECT STUFF(LEFT('0' + CONVERT(VARCHAR(20), CAST(@date AS TIME(0)), 9), 11), 9, 0, ' ')

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

adonetok (11/7/2011)
The code below will display as '21:30:00 PM'
How to modify code to display as '09:30:00 PM'?
declare @date datetime
set @date = '2011-11-08 21:30:00.000'
select CONVERT(VARCHAR, @date, 108) + ' ' + Substring(CONVERT(VARCHAR, @date, 100), 18, 2)

Everyone else has concentrated on displaying what you want. I'd like to know what you're going to do with the "display" value once you have it. The reason I ask is that storing such formatted data in a table is a form of "death by SQL" that you'll live to regret over and over and... . 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Jeff Moden (11/7/2011)
adonetok (11/7/2011)
The code below will display as '21:30:00 PM'
How to modify code to display as '09:30:00 PM'?
declare @date datetime
set @date = '2011-11-08 21:30:00.000'
select CONVERT(VARCHAR, @date, 108) + ' ' + Substring(CONVERT(VARCHAR, @date, 100), 18, 2)
Everyone else has concentrated on displaying what you want. I'd like to know what you're going to do with the "display" value once you have it. The reason I ask is that storing such formatted data in a table is a form of "death by SQL" that you'll live to regret over and over and... . 😉

Jeff...from an earlier thread by same OP .....

http://www.sqlservercentral.com/Forums/FindPost1201498.aspx

adonetok (11/7/2011)
I need pass exact string format from getdate() to third party application in which I can not do anything.
The third party applicaton only take format like below(including space):
'11/07/2011 09:30:00 AM'
'12/11/2011 03:15:00 PM'

unfortunately OP didnt explain reasoning in this post.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

We should frame this post as one of the reasons to not double post. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)