Convert computed column into a Date Type

Question

Convert computed column into a Date Type

LillyDBA

Mr or Mrs. 500

Points: 515
More actions
December 3, 2010 at 9:43 am

#227852

How can I convert the CalDate column into a Date type column(SQL Server 2008)? I need the CalDate column to be a Date datatype rather than DateTime as this script will produce:
CREATE TABLE [dbo].[MyTable](
[MyTableId] [bigint] NOT NULL,
[MyDate] [datetime] NOT NULL,
[CalDate] AS (dateadd(day,datediff(day,(0),[ModDate]),(0))) PERSISTED NOT NULL,
CONSTRAINT [MyTablePK] PRIMARY KEY CLUSTERED
(
[MyTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [MyTableFK.MyTableId] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyMainTable] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MyTable] NOCHECK CONSTRAINT [MyTableFK.MyTableId]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [MyTableDF.MyDate] DEFAULT (getdate()) FOR [ModDate]
GO
Thank you in advance!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

just alter the definition of the computed column so it includes a cast or convert;

however, since a DATE datatype doesn't have the time portion, i think you could get rid of the whole dateadd/datediff stuff.

[CalDate] AS CONVERT(date,(dateadd(day,datediff(day,(0),[ModDate]),(0))) ) PERSISTED NOT NULL,

/*--results

2010-12-03

*/

select convert(date,getdate())

[CalDate] AS CONVERT(date[/b[ModDate] ) PERSISTED NOT NULL,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LillyDBA Mr or Mrs. 500 Points: 515 More actions · Answer 2

Lowell,

Thank you for your help!

Your solution worked!

Lilly

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 3

Lowell (12/3/2010)
just alter the definition of the computed column so it includes a cast or convert;
however, since a DATE datatype doesn't have the time portion, i think you could get rid of the whole dateadd/datediff stuff.
[CalDate] AS CONVERT(date,(dateadd(day,datediff(day,(0),[ModDate]),(0))) ) PERSISTED NOT NULL,
/*--results
2010-12-03
*/
select convert(date,getdate())
[CalDate] AS CONVERT(date[/b[ModDate] ) PERSISTED NOT NULL,

If it were for DATETIME instead of just DATE, you don't need the DATEADD. Just convert the DATEDIFF to a DATETIME. Saves a couple of clock cycles for large updates. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)