DateTime Format Conversion AM/PM

Question

DateTime Format Conversion AM/PM

HeftSteady

Old Hand

Points: 337
More actions
November 29, 2022 at 1:41 am

#4119229

Hi,

I have a field showing select field from table ;
2022-11-28 16:19:38
I would like to convert to 11-28-2022 4:19 PM

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Replace GETDATE() in the following with your datetime valued.

 SELECT CONVERT(CHAR(10),GETDATE(),110)+STUFF(RIGHT(CONVERT(CHAR(19),GETDATE(),100),8),7,0,' ')

Some will suggest using the FORMAT function... I strongly recommend against that. See the article at the following link... I can vouch for the author. 😀

https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format

I also recommend that you bone up on the CONVERT function. Here's the link for that.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

--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)

HeftSteady Old Hand Points: 337 More actions · Answer 2

Your solution did not convert to 12 hour or include AM PM designation.

Ha, I know you suggested against using FORMAT but that is what I ended up doing .

FORMAT(CAST(field_here AS datetime), 'MM/dd/yyyy hh:mm tt') 'field_name_here'

(I am not an expert by any means).

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

HeftSteady wrote:

Your solution did not convert to 12 hour or include AM PM designation.
Ha, I know you suggested against using FORMAT but that is what I ended up doing .
FORMAT(CAST(field_here AS datetime), 'MM/dd/yyyy hh:mm tt') 'field_name_here'
(I am not an expert by any means).

I don't post untested code. Please post the exact code that you say didn't work and identify the datatype you used to replace GETDATE() with.

Here's the code I posted and the results it returned.

--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)