Ask the Expert

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.

    Requires Free Membership to View

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:


Do you have comments on this Ask the Expert Q&A? Let us know.

This was first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: