Post reply

Getting the Time part out of a DateTime

  • January 29, 2007 at 11:15 am

    #62119

    Hi folks,

    I have a table with two DateTime fields (and of course some others). Now I tried to create a view to get all the data out of the table but for the DateTime fields I only need the time information. I tried to use DatePart but I only can get hours or minutes or another part. Is there a possibility to get the "12:00" out of "01.01.2007 - 12:00:00" ?

    Thanx a lot for your help

  • January 29, 2007 at 11:30 am

    #686217

    Take a look at the Convert function

     

    CONVERT(varchar(20), {datetime field}, 8)

     

    You can also use 14 in place of the 8 to get milliseconds

     

    Dave

  • January 29, 2007 at 11:43 am

    #686227

    Thanx a lot. I combined the Convert with the LEFT function to get rif of the seconds.

    LEFT (CONVERT (varchar(20); {datetime}; 8); 5)

  • January 30, 2007 at 10:07 am

    #686517

    If you don't want the entire string returned by CONVERT, use a CHAR datatype to limit what you keep.

    Try CONVERT(CHAR(5), [datetime], 8)

  • January 30, 2007 at 12:55 pm

    #686580

    following always helped with relation to any datetime related queries

    http://www.sql-server-helper.com/tips/date-formats.aspx

  • January 30, 2007 at 5:51 pm

    #686657

    This will give the time as an offset from midnight on 1900-1-1, the SQL Server zero day.

    -- Subtract Date from Datetime to get Time on 1900-01-01
selectMyTime = MyDate-dateadd(dd,datediff(dd,0,MyDate),0)
from
( Select MyDate = getdate() ) a

Viewing 6 posts - 1 through 5 (of 5 total)

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