DateTime Format Conversion AM/PM

  • 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

  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

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

     

     

     

  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

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

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