Post reply

Date Format (mm/dd/yyyy hh:mm), no seconds or milliseconds

  • February 9, 2009 at 1:32 pm

    #130795

    Does anyone know syntax to use to format a date field with as follows (02/09/2009 14:34)?

    Any help would be appreciated.

    Thanks,

    Jeff

  • February 9, 2009 at 2:03 pm

    #940810

    Try this:

    SELECT CONVERT(VARCHAR, GetDate(), 101) + ' ' +

    CONVERT(VARCHAR, DATEPART(hh, GetDate())) + ':' +

    RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, GetDate())), 2) AS Date

    Greg

  • February 9, 2009 at 2:06 pm

    #940812

    That worked.

    Thank you very much.

  • February 9, 2009 at 2:14 pm

    #940818

    Although Greg's solution works, you should still pose the question if you need to do this in a query!

    Presentation should be handled at the front end application !

    (one of the advantages is that you'll be able to use the client settings to format the data like the consumer wants it to be)

    That goes for datetime, numeric data ( decimal point as comma or not),..

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 9, 2009 at 2:17 pm

    #940820

    This is for a data extract to a 3rd party software. So Greg's solution fits our need perfectly.

    Thanks,

  • February 9, 2009 at 9:58 pm

    #940954

    Here is another method that is a bit simpler:

    select convert(char(10), getdate(), 101)

    + ' ' + convert(char(5), getdate(), 108);

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 9, 2009 at 10:07 pm

    #940956

    In that case, this might be just a little faster because it doesn't have as many CONVERT's...

    SELECT CONVERT(CHAR(11),GETDATE(),101)

    + CONVERT(CHAR( 5),GETDATE(),114)

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

  • February 10, 2009 at 7:36 am

    #941203

    Thanks for everyone's reply's.

    Have a great day..

  • December 16, 2009 at 3:01 am

    #1092883

    For the format : -- MM/DD/YYYY HH:MMAM/PM

    CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7)

  • January 12, 2010 at 3:51 am

    #1102278

    Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

    And

    Select convert(varchar(16),Getdate(),120) as [Date]

  • January 12, 2010 at 11:27 pm

    #1102887

    imhemal29 (1/12/2010)

    Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

    And

    Select convert(varchar(16),Getdate(),120) as [Date]

    Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

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

  • January 12, 2010 at 11:58 pm

    #1102895

    Jeff Moden (1/12/2010)

    Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

    Assuming you don't mind using the Microsoft version of the Kuwaiti algorithm of the Islamic (Hijri) calendar *and* you're all sensible and use dd/mm/yyyy instead of the bizarre mm/dd/yyyy format...:-D...this works:

    SELECT CONVERT(CHAR(16), GETDATE(), 131)

    :laugh:

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • January 13, 2010 at 5:22 am

    #1102974

    Paul White (1/12/2010)

    Jeff Moden (1/12/2010)

    Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

    Assuming you don't mind using the Microsoft version of the Kuwaiti algorithm of the Islamic (Hijri) calendar *and* you're all sensible and use dd/mm/yyyy instead of the bizarre mm/dd/yyyy format...:-D...this works:

    SELECT CONVERT(CHAR(16), GETDATE(), 131)

    :laugh:

    Now THAT's funny! 😛

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

  • December 13, 2010 at 8:49 am

    #1261753

    Jeff Moden (1/12/2010)

    imhemal29 (1/12/2010)

    Select Cast(convert(varchar(16),Getdate(),120) as DateTime) as [Date]

    And

    Select convert(varchar(16),Getdate(),120) as [Date]

    Check again... op asked for mm/dd/yyyy hh:mm. Above code returns yyyy-mm-dd hh:mi 😉

    Could not resist to post that imhemal29 solution might not be satisfying the OP query but it luckily provided me the answer I was searching for in solving this chalenge

    TSQL Challenge 37 - Calculate the downtime and duration of servers based on the monitoring log

    Thanks imhemal29

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • December 19, 2018 at 2:05 am

    #2016797

    For the layout...
    2018-02-14 18:28
    use...
    LEFT(CONVERT(VARCHAR, SalesOrders.CreatedOn, 121),16)

Viewing 15 posts - 1 through 15 (of 17 total)

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