Q

Calculate day gap and create a new column for the results

I would like to calculate the day gap and create a new column for the results. Here is my code:
SELECT
DATEDIFF(day,FromDOS,ThruDOS) as LengthOfStay FROM tbl_Master 

I want my new column to be added to the table I have queried. I have tried several coding options and scoured my SQL books to no avail. I know this must be simple. That is what makes it so frustrating.

Here is some simple code for the table that has LengthofStay as a computed column to properly calculate the number of days between the two date columns:
/****** Object:  Table [dbo].[tbl_Master]    Script Date: 
5/9/2005 9:45:17 AM ******/
CREATE TABLE [dbo].[tbl_Master] (
 [LOSID] [int] NOT NULL ,
 [FromDOS] [datetime] NOT NULL ,
 [ThruDOS] [datetime] NOT NULL ,
 [LengthofStay] AS (datediff(day,[FromDos],[ThruDos])) 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_Master] WITH NOCHECK ADD 
 CONSTRAINT [PK_tbl_Master] PRIMARY KEY  CLUSTERED 
 (
  [LOSID]
 )  ON [PRIMARY] 
GO

Here is a screen shot with the sample data:

Figure 1


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in May 2005
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close