Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Unanswered: need to calculate the total hours mintues

    need to calculate the total hours mintues in sql server and asp.net

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ? total hours based on what
    start time + duration/elapsed
    sum of duration/elapsed

    how is your data currently stored (column names, datatypes
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    6
    USE [bramandam]
    GO
    /****** Object: Table [dbo].[aten] Script Date: 07/05/2014 13:00:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[aten](
    [Id] [int] NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Intime] [datetime] NULL,
    [Date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Outtime] [datetime] NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF


    my output


    Intime Outime

    14:52:00 15:18:00 3 hrs 26min
    14:52:00 15:18:00 3 hrs 26min



    =======================================

    6hrs 52min

    ================================================== ===

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select sum(datediff("hh",intime,outtime) as ElapsedTime
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2014
    Posts
    6

    error

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'ElapsedTime'.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    healdem probably meant:
    Code:
    select sum(datediff("hh",intime,outtime)) as ElapsedTime
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2014
    Posts
    6

    Unhappy

    but it shows as a -6 .but it never shows the mintues i need mintues .

    my table data are

    id name staus section
    1 celv Present BM 1900-01-01 14:52:00.000 1/07/2014 1900-01-01 13:52:00.000
    1 celv Present BM 1900-01-01 14:52:00.000 1/07/2014 1900-01-01 13:52:00.000
    1 celv Present BM 1900-01-01 14:52:00.000 1/07/2014 1900-01-01 13:52:00.000
    1 celv Present BM 1900-01-01 17:00:00.000 1/07/2014 1900-01-01 13:52:00.000



    after execute ur query i got the answer as
    -7

    but it has -7.8 mintues'
    it shows as (minus)
    Last edited by celv; 07-06-14 at 04:26.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ive not used SQL server in a serious capacity. I guess you could (probably) do something like:-
    select sum(outtime - intime) as ElapsedTime from mytable

    I know such dirty tricks work in Access (JET) and other db's but as to whether it works in SQL server you will have to try for yourself

    Assuming it does work
    then you'd have the difference in date time
    do formatting that adequately would give you the value you are looking for
    again how you;d do that in SQL server I dunno, no doubt google can provide soem references for you.

    like all db's SQL server has a notional base/reference date so presenting the data as a calendar date will have no relevant meaning.

    you may have to resort to datediff to extract days, hours, minutes & seconds
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2014
    Posts
    6

    query

    but i have this code
    select id
    , convert(char(20),Intime,120) as i
    , convert(char(20),Outtime,120) as o
    , convert(char(20),DateDiff(hh, Intime, Outtime)) as t
    from aten


    i shows the time difference for each row but it nver shows the total. but same thing it never show the mintues

    please help to calculate the minutes and hours .....

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well first off use the correct datatype for the type of values you want to store

    for a date / time value that means use a date/time type
    Data Types (Transact-SQL)
    that means date, datetime2 or datetime


    you could probably use convert as a kludge after thought to patch up a dodgy design but its just dumb not to fix the problem as opposed to patch it up and pretend its gone away


    but its so nasty, and im not familiar with convert or cast so you'd have to work it out yourself form here:-
    Data Types (Transact-SQL)
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    This ought to get you close enough to modify as you need:

    CONVERT(varchar,datediff(hour,@InTime, @OutTime),14) + ' Hours ' + CONVERT(varchar,datediff(minute,@InTime, @OutTime),14) + ' Minutes' as ElapsedTime

  12. #12
    Join Date
    Jul 2014
    Posts
    6
    am getting this error

    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@Intime".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •