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

  • jpalmer-755805

    SSC Eights!

    Points: 959

    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

  • JestersGrind

    SSCertifiable

    Points: 5439

    Try this:

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

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

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

    Greg

  • jpalmer-755805

    SSC Eights!

    Points: 959

    That worked.

    Thank you very much.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • jpalmer-755805

    SSC Eights!

    Points: 959

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

    Thanks,

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88116

    Here is another method that is a bit simpler:

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

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

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 994661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jpalmer-755805

    SSC Eights!

    Points: 959

    Thanks for everyone's reply's.

    Have a great day..

  • Phani-345480

    SSC Veteran

    Points: 228

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

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

  • imhemal29

    SSC Enthusiast

    Points: 144

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

    And

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

  • Jeff Moden

    SSC Guru

    Points: 994661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Paul White

    SSC Guru

    Points: 150442

    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:

  • Jeff Moden

    SSC Guru

    Points: 994661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Mohammad Mazharuddin Ehsan

    SSCarpal Tunnel

    Points: 4548

    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]

  • lee 90891

    Newbie

    Points: 9

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

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

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