locked
Date with HH:MM appended ? RRS feed

  • Question

  • HI,

    How do i get the mentioned format. if today is 7th July then,

    2015-07-07 15:30

    i.e. date with HH and MM appended in today's date ?

    want just one simple statement. don't want to write big query as i want to use this condition in one of the joining criteria.

    Regards

    Tuesday, July 7, 2015 5:56 AM

Answers

  • Hello - You can try this:

    SELECT REPLACE( CONVERT ( VARCHAR, GetDate(), 102), '.', '-') + ' ' + 
    LEFT ( CONVERT ( VARCHAR, GetDate(), 108), 5 )

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by UltraDev Tuesday, July 7, 2015 7:17 AM
    Tuesday, July 7, 2015 6:08 AM

All replies

  • Hello - You can use this:

    SELECT REPLACE( CONVERT ( VARCHAR, GetDate(), 102), '.', '-') + ' ' + CONVERT ( VARCHAR, GetDate(), 108)

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, July 7, 2015 6:00 AM
  • almost right but i want only till minute.

    your exp. is giving "seconds" as well. 

    output is : 2015-07-07 13:03:42

    while i want is : 2015-07-07 13:03

    (with seconds eliminated)

    regards

    Tuesday, July 7, 2015 6:05 AM
  • Hello - You can try this:

    SELECT REPLACE( CONVERT ( VARCHAR, GetDate(), 102), '.', '-') + ' ' + 
    LEFT ( CONVERT ( VARCHAR, GetDate(), 108), 5 )

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by UltraDev Tuesday, July 7, 2015 7:17 AM
    Tuesday, July 7, 2015 6:08 AM
  • got it thanks
    Tuesday, July 7, 2015 7:18 AM
  • --SQL Server 2012  or above
    SELECT  format(getdate(),'yyyy-MM-dd hh:mm')

     
    Tuesday, July 7, 2015 1:08 PM
  • Please read any book on SQL. Why do you want to invent your own programming language? 

    The ANSI/ISO standards define a TIMESTAMP(n) temporal data type it is made up of a DATE and a TIME. The date fields (yes, they are called fields!)  are {YEAR, MONTH, DAY} and they are displayed as "yyyy-mm-dd", where the year field is between "0001" and "9999" for its range. This is based on the ISO-8601; not knowing this standard and being in IT is like being an engineer who does not know the Metric System. 

    TIME is defined as {HOUR, MINUTE, SECOND} where decimal seconds are defined a 0 to an implementation precision. This is usually 7 places in modern hardware. 

    In the Microsoft dialect, we use DATEIME2(n) as the data type name. 

    If you need to round off the seconds, then do it in the DDL. I find that people usually need 10 or 15 minute timeslots,  so this will work:

    CREATE TABLE Foobars
    ( ..
     foo_date DATETIME2(0) NOT NULL
         CHECK (CAST (foo_date AS TIME) 
                   IN ('00:00:00', '00:15:00', ...'23:45:00'),
     ..);



    <removes the signature, which include advertising, as part of CELKO's abuse treatment>
    • Edited by pituachMVP Friday, July 17, 2015 11:15 AM removes the signature
    Tuesday, July 7, 2015 3:50 PM