none
Extract Time from DateTime field

    Question

  • I have a DateTime field in SQL 2000 which of course stores data like so Apr 3 2013 12:03PM.  

    I know I can use CONVERT(VARCHAR(11), fieldName, 101) to extract Apr 3 2013, but how do I leave all that, and ONLY extract the 12:03?

    Wednesday, April 03, 2013 4:04 PM

Answers

All replies

  • Have you tried with the style 108 or 114 ?

    CONVERT(VARCHAR(11), fieldName, 114)
    CONVERT(VARCHAR(11), fieldName, 108)



    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

    Wednesday, April 03, 2013 4:08 PM
  • DECLARE @d DATETIME='Apr 3 2013 12:03PM' 
    
    SELECT @d
    
    SELECT LEFT(CONVERT(VARCHAR(12),@d,108),5)
    SELECT LEFT(CONVERT(VARCHAR, @d, 24),5)
    SELECT LEFT(CONVERT(VARCHAR(12),@d,114),5)


    Narsimha

    • Marked as answer by IndigoMontoya Wednesday, April 03, 2013 5:15 PM
    Wednesday, April 03, 2013 4:09 PM
  • Have you tried with the style 108 or 114 ?

    CONVERT(VARCHAR(11), fieldName, 114)
    CONVERT(VARCHAR(11), fieldName, 108)



    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

    Both of these suggestions still only return the data.  I want to return the time, the right half of the field.
    Wednesday, April 03, 2013 4:51 PM
  • Have you tried with the style 108 or 114 ?

    CONVERT(VARCHAR(11), fieldName, 114)
    CONVERT(VARCHAR(11), fieldName, 108)



    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

    Both of these suggestions still only return the data.  I want to return the time, the right half of the field.

    His code does exactly what you are asking

    DECLARE @myDate datetime
    SET @myDate = GETDATE()
    SELECT CONVERT(VARCHAR(11), @myDate, 114)
    ,CONVERT(VARCHAR(11), @myDate, 108)

    Results:

    12:06:49:52  12:06:49

    Depends on whether you want milliseconds as far as what format you choose


    Chuck Pedretti | Magenic – North Region | magenic.com


    Wednesday, April 03, 2013 5:07 PM
  • That was oversight on my part..I missed that post.  When I sad 'both' solutions I was referencing the two options posted in the quoted post.
    Wednesday, April 03, 2013 5:15 PM