Results 1 to 5 of 5

Thread: Time

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Time

    Hi guys I am trying to calculate two colums that say
    Beginning Date 5:00pm
    Ending Date 7:00pm
    in a table from a database I inherted. The data type for these two is nvarchar(255)

    Code:
    CREATE TABLE [dbo].[StudentActivity](
    	[Event Key field] [int] NOT NULL,
    	[Activity Date] [nvarchar](255) NULL,
    	[Event ID] [nvarchar](255) NULL,
    	[Activity Location] [nvarchar](255) NULL,
    	[Event Name] [nvarchar](255) NULL,
    	[Primary Catagory] [nvarchar](255) NULL,
    	[Event Address] [nvarchar](255) NULL,
    	[Explain Other] [nvarchar](255) NULL,
    	[Activity] [nvarchar](255) NULL,
    	[Event City] [nvarchar](255) NULL,
    	[Beginning Time] [nvarchar](255) NULL,
    	[Ending Time] [nvarchar](255) NULL,
    	[Total Time] [float] NULL,
    	[First Subject] [nvarchar](255) NULL,
    	[Total 1st Subject  Hours] [float] NULL,
    	[Second Subject] [nvarchar](255) NULL,
    	[Total 2nd Subject Hours] [float] NULL,
    	[Third Subject] [nvarchar](255) NULL,
    	[Total 3rd Subject Hours] [float] NULL,
    	[Fourth Subject] [nvarchar](255) NULL,
    	[Total 4th Subject Hours] [float] NULL,
    	[Student First Name] [nvarchar](255) NULL,
    	[Student Last Name] [nvarchar](255) NULL,
    	[ParentID] [nvarchar](255) NULL,
    	[Instructor] [nvarchar](255) NULL,
    	[Accommodations made] [nvarchar](255) NULL,
    	[Covered during Session] [nvarchar](255) NULL,
    	[Problems] [nvarchar](255) NULL,
    	[Other Comments] [nvarchar](255) NULL,
    	[Services] [nvarchar](255) NULL,
    	[State Category] [nvarchar](255) NULL,
    	[State Services] [nvarchar](255) NULL,
     CONSTRAINT [PK_'Student Activity$'] PRIMARY KEY CLUSTERED 
    (


    I am trying to get it add the two colums (this is the format I got it in)

    Beginning Date + Ending Date = 2.0hrs

    5:00pm + 7:00pm = 2.0hrs

    How exactly would I do that?
    Last edited by desireemm; 05-27-09 at 19:26.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First, you change the data type for the columns to DATETIME...

    Oh wait, you probably want to do this the hard way. I can do that too!

    Code:
    SELECT DateDiff(second, b, e) / 3.6e3
       FROM (SELECT [Beginning Time] AS b, [Ending Time] AS e
          FROM [dbo].[StudentActivity] AS z1
          WHERE  1 = IsDate([Beginning Time])
             AND  1 = IsDate([Ending Time])) AS z2
    Chew on this one for a while, it should entertain you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If this is SQL 2008, you can start to take advantage of the new TIME datatype. But that would require you impose some rework on the client, which they probably will not go for.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you tell us something about the history/origin of this table design? This looks like an automatically generated table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pat, that's not nearly NZDFy enough!

    Desi - the answer is always going to be "use the correct datatype", which is either datetime, or as MCrowley has mention time in SQL Server 2008.

    With the appropriate datatype, you can use easy-peasy inbuilt functions such as DateDiff() to get the answer required.
    George
    Home | Blog

Posting Permissions

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