none
Date and Time from Getdate

    Question

  • Hi All,

    I want to retrive date and time from Getdate.

    from the below, i want ti display date and time.

    2012-05-25 11:20:20:633

    Out Put: 2012-05-25 11:20

    Thanks,

    Mandava

    Monday, April 29, 2013 5:00 PM

Answers

All replies

  • In SQL 2008 and up just use

    select cast(datetimeColumn as DATE) as DateOnly, cast(datetimeColumn as time) as TimeOnly ...

    In SQL 2005 and less try

    select convert(varchar(10), DatetimeColumn, 112) as [DateOnlyInCharForm], convert(varchar(10), DatetimeColumn, 118) as TimeOnly

    Check CAST & CONVERT topic in BOL for more formats you can use.


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


    My blog

    Monday, April 29, 2013 5:04 PM
  • There is no a style like you want for CONVERT function, but you can combine with LEFT function like this:

    select convert( varchar, getdate(), 120 )
    , left( convert( varchar, getdate(), 120 ) , 16 )

                                   
    ------------------------------ ----------------
    2013-04-29 12:01:00            2013-04-29 12:01

    (1 row(s) affected)

    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

    Monday, April 29, 2013 5:05 PM
  • I am looking for below result as a single column.

    2012-05-25 11:20

    Thanks,

    Mandava.

    Monday, April 29, 2013 5:08 PM
  • SELECT CAST(CAST(GETDATE() AS DATE) AS VARCHAR(25))
    	+' '+LEFT(CONVERT(VARCHAR(12),GETDATE(),108),5) AS Result


    Narsimha

    Monday, April 29, 2013 5:10 PM
  • Hi,

    Try like this,

    SELECT REPLACE(CONVERT(CHAR(16),GETDATE(),126),'T',' ') Output


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Monday, April 29, 2013 5:22 PM
  • check this
    select cast (convert (date,getdate()) as varchar(10))+' ' + convert(varchar(5), GETDATE(), 108)


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, April 29, 2013 5:44 PM
  • SELECT FORMAT( GETDATE(), 'yyyy-MM-dd HH:mm', 'en-US' ) AS 'dt'
    , FORMAT(Cast('2012-05-25 11:20:20:633' as datetime), 'yyyy-MM-dd HH:mm', 'en-US' ) AS 'YourDTFormat'

    Monday, April 29, 2013 6:03 PM
  • Why? In general, the client app will format the data as per the users liking. I.e., SQL Server returns datetime datatype (whcih is binary data = no format) and the client app will do the formatting. You can convert it to string, of course, as suggested by posts here but I recommend having the client app do the formatting.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, April 29, 2013 7:11 PM
  • You can trim it to the second : 

    CAST (CURRENT_TIMESTAMP AS DATETIME(0))

    Do you really need this to the nearest minute? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, April 29, 2013 7:39 PM