none
milliseconds

    Question

  • Hi,

    I have a field in milliseconds which I like to convert to hh:mm, see "mutation_time".

    I was able to solve this for the date, but now the time...

    Anybody with the solution?

    Thx

    msc_history_record.mutation_time
    SELECT  CONVERT(varchar(12),dateadd(dd,msc_history_record.created_date,`DEC 31 1971`),104) AS 
    DATE_1, 
            ac_registr AS "ACreg",
    		msc_history_record.seqno AS "TC SeqNo(-1 = SRT)",
            msc_taskcard.taskcardno AS "Taskcard", 
    	 	msc_taskcard.title AS "TC_Description",
            msc_history_record.closing_date AS "Date_closed",
            msc_history_record.mutation_time AS "Time_mutation",
            msc_history_record.release_time,
            msc_history_record.mech_sign,
    		msc_history_record.closing_date,
    		msc_history_record.perf_tah,
    		msc_history_record.perf_tac
      FROM    msc_history_record, 
    	 	  msc_taskcard   
     WHERE ac_registr = 'EZA'     
       AND  msc_taskcard.taskcardno_i=msc_history_record.taskcardno_i
    AND msc_history_record.created_date BETWEEN datediff(day,"DEC 31 1971",getdate()) - 30 AND datediff(day,"DEC 31 1971",getdate())
    

    Friday, June 21, 2013 8:45 PM

All replies

  • I have a field in milliseconds which I like to convert to hh:mm, see "mutation_time".

    As long as the milliseconds values is not over 86400000 (24 hours), try:

    CAST(DATEADD(millisecond, msc_history_record.mutation_time, '') AS time) AS mutation_time


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 22, 2013 2:39 AM
  • Hello,

    try this

    CONVERT(varchar, DATEADD(millisecond,

    msc_history_record.mutation_time

    , 0), 114) AS Time_Mutation

    thanks

    Saturday, June 22, 2013 3:31 AM
  • Thx

    Unfortunately this gives only zeros 00:00:00, despite the value are below 86400000. E.g. 25'984'960...

    Sunday, June 23, 2013 12:21 PM
  • Thx...

    Unfortunately, this gives an error "Function DATEADD with wrong number or type of argument(s)"

    Sunday, June 23, 2013 12:23 PM
  • Unfortunately this gives only zeros 00:00:00, despite the value are below 86400000. E.g. 25'984'960...

    I get fractional seconds when I run the script from SSMS.  Perhaps the front-end is truncating the result milliseconds?  You might try the version below to round to the nearest second.

    CREATE TABLE dbo.msc_history_record(
    	mutation_time int NOT NULL
    	);
    
    INSERT INTO dbo.msc_history_record VALUES
    	(25),(984),(960);
    
    SELECT
    	CAST(DATEADD(millisecond, msc_history_record.mutation_time, CAST('' AS datetime2(3))) AS time(0)) AS mutation_time
    FROM dbo.msc_history_record;
    

    Result from SSMS:

    mutation_time
    00:00:00
    00:00:01
    00:00:01


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 23, 2013 1:40 PM