locked
Current Date with Time Only RRS feed

  • Question

  • How can I get the current time stamp to look like this   "11:04 AM"?  I just need the time and whether it is AM or PM..

    http://www.isolutionspartners.com/

    Wednesday, September 12, 2012 3:36 PM

Answers

All replies

  • Try

    substring(CONVERT(varchar,getdate(),100),12,9)


    Many Thanks & Best Regards, Hua Min

    Wednesday, September 12, 2012 3:50 PM
  • In SQL Server 2012 we can use new FORMAT function, e.g.

    DECLARE @d DATETIME = GETDATE();
    SELECT FORMAT( @d, 'hh:mm tt', 'en-US' ) AS Result

    See details at

    http://www.databasejournal.com/features/mssql/the-format-function-in-sql-server-2012.html

    In SQL Server prior versions try

    SELECT RIGHT(convert(VARCHAR(50), @d, 100), 7)
    See also 

    Formatting the time from a datetime or time datatype by using the STUFF function



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog




    • Edited by Naomi N Wednesday, September 12, 2012 4:07 PM
    • Proposed as answer by Puneet.Sh Wednesday, September 12, 2012 5:35 PM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:36 PM
    • Unmarked as answer by Kalman Toth Tuesday, September 18, 2012 2:44 PM
    • Unproposed as answer by Kalman Toth Tuesday, September 18, 2012 2:44 PM
    • Marked as answer by Kalman Toth Wednesday, September 19, 2012 7:09 AM
    Wednesday, September 12, 2012 3:50 PM
  • Check function CONVERT in BOL.

    SELECT STUFF(RIGHT('0' + LTRIM(RIGHT( + CONVERT(varchar(50), GETDATE(), 100), 7)), 7), 6, 0, ' ') AS tm;
    GO

    If you are using SQL Server 2012, then check FORMAT function.

    AMB

    Some guidelines for posting questions...

    • Proposed as answer by SQL Novice 01 Wednesday, September 12, 2012 3:55 PM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:36 PM
    • Unmarked as answer by Kalman Toth Tuesday, September 18, 2012 2:44 PM
    • Unproposed as answer by Kalman Toth Tuesday, September 18, 2012 2:44 PM
    • Marked as answer by Kalman Toth Wednesday, September 19, 2012 7:09 AM
    Wednesday, September 12, 2012 3:52 PM
  • How can I get the current time stamp to look like this   "11:04 AM"?  I just need the time and whether it is AM or PM..

    http://www.isolutionspartners.com/

    try this,

         SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5) 
    + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)


    ANK HIT - if reply helps, please mark it as ANSWER or helpful post

    • Proposed as answer by ank hit Wednesday, September 12, 2012 3:54 PM
    • Unproposed as answer by ank hit Wednesday, September 12, 2012 3:54 PM
    Wednesday, September 12, 2012 3:54 PM
  • 

    SELECT SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),13,19)
    --OR 
    SELECT RIGHT(CONVERT(VARCHAR(20),GETDATE(),100),7)
    --OR 
    --To have space between Time and AM / PM
    SELECT SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),13,5) + ' ' + SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),18,2)



    • Edited by Naarasimha Wednesday, September 12, 2012 4:39 PM
    Wednesday, September 12, 2012 4:17 PM
  • How can I get the current time stamp to look like this   "11:04 AM"?  I just need the time and whether it is AM or PM..

    In that case just use the DATEPART function:

    DECLARE @d DATETIME = GETDATE();
    SELECT CONVERT(varchar, @d, 8);
    -- 13:11:47
    SELECT CASE WHEN DATEPART(HH, @d) < 12 THEN 'AM' 
           ELSE 'PM'END AS Result;
    -- PM

    More on datetime functions:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Tuesday, September 18, 2012 5:13 PM
    Tuesday, September 18, 2012 2:44 PM
  • pls try

    select replace(replace(right('00'+ltrim(right(convert(varchar(50),getdate(),100),8)),7),'AM',' AM'),'PM', ' PM')

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, September 18, 2012 2:55 PM