FORMULA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FORMULA

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-22 : 09:51:19

I have a table with 1 columns in it

CREATE TABLE [dbo].[date] (
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_week] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

I want to aaply the following formulas to the last 3 columns, but when I enter the formula in the on the design table screen, it doesn't like the syntax. I am entering the following for each column
datepart(DW,Date_occured_from)
datepart(DD,Date_occured_from)
datepart(DY,Date_occured_from)
Can someone help with the correct syntax for doing this please. Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-22 : 10:16:19

I'm not sure that i understand you, is this a typo "Date_occured_from"
Why dont you just do these calculations as and when required?? Those last 3 columns are just taking up unneccessary space
Change your data type for Date_updated to datetime/smalldatetime - it will make it easier to do calculations/functions on

eg
SELECT Date_updated, datepart(DW,Date_updated), datepart(DD,Date_updated), datepart(DY,Date_updated)

andy

Beauty is in the eyes of the beerholder

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-22 : 10:23:08

Thanks Andy. I am trying to do this so I only need to insert values into the first column and the remaining columns are computed off the first. By defining formula, I figured I would be saving space , not using more space as it doesn't really store the data in the table, just computes it on the fly.

Do you know how I would enter these formulas. Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-22 : 10:36:54

A char data type will use the space even if there is no data in them! See BOL

Why not create the table with 1 column (date data type) then create a view


CREATE TABLE [dbo].[date] (
[Date_updated] [datetime] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[date2]
AS
SELECT Date_updated, datepart(DW,Date_updated) AS Day_of_week, datepart(DD,Date_updated)AS Day_of_month, datepart(DY,Date_updated) AS Day_of_year
FROM [dbo].[date]
GO

SELECT * FROM [dbo].[date2]

Then base all your queries against the VIEW and not the table

Andy

Beauty is in the eyes of the beerholder

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-22 : 11:04:30

Andy thanks again for the tip on views... however, I want to use the Date table. I think I know the problem:

I have to cast the column to a datetime first and then the formula will work.

datepart(DW, Cast(Date_Updated As DateTime))
datepart(DD, Cast(Date_Updated As DateTime))
datepart(DY, Cast(Date_Updated As DateTime))

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-22 : 13:52:18

quote:
however, I want to use the Date table. I think I know the problem:

Your choice

Have a look at this thread
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48734[/url]

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources