Post reply

Time part of datetime column

  • September 5, 2006 at 9:31 am

    #153172

    Hi i have a column in my table were i want only the date part of it to be placed into another column how do i do this i've tried .

    update tbl_metrics_asp_test

    set New_idle_duration = convert(varchar(8), New_idle_duration, 108),

    New_idle_Time = convert(varchar(8), New_idle_Time, 108)

    from tbl_metrics_asp_test

     

    but i keep getting the full date  "1900-12-31 12:29:56.000"

    all i want displayed is "12:29:56"

     

    The data type of my new column has to be datetime as i need to do calculation on the time..

  • September 5, 2006 at 9:47 am

    #658574

    select convert(char(20),getdate(),108)

  • September 5, 2006 at 9:50 am

    #658575

    If you just want the time part or just the date part to be stored then you need to make that column it a varchar or char column. If you have a column as Date then SQL will stote date and time.

    Down side to it is if you want to do date comparision or date function you will have to convert it back. If you want it to "display" just the date part use a view to convert to Character.

    Hope this helps

    Thanks

    Sreejith

  • September 5, 2006 at 9:54 am

    #658577

     

    This works:

    SELECT   substring(convert(char,New_idle_duration,114),1,8) as time

     

    --NS

  • September 5, 2006 at 9:57 am

    #658578

    If you store this in a column thats datetime you will have 1/1/1900 as the Datepart.

    Thanks

    Sreejith

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

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