Formulas

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_w] [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. ThanksI do not see column [Date_occured_from] in the table definition.
Example:
use northwind
go
create table t (
colA datetime default (getdate()),
colB as cast(right('0' + ltrim(datepart(dw, colA)), 2) as char(2))
)
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"Munch" wrote:

> 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_w] [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
>|||"Enter the formula" ' Are you trying to create computed columns?
If so, the column [Date_occured_from] must exists in the table, and it must
be a datetime, or smalldatetime datatype.
The only column in your table that appears to be a date is Date_Updated, and
it is typed as a Char(8)... If you want to use that, you will have to cat it
to a datetime first, and use the correct column name
datepart(DW, Cast(Date_Updated As DateTime))
datepart(DD, Cast(Date_Updated As DateTime))
datepart(DY, Cast(Date_Updated As DateTime))
"Munch" wrote:

> 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_w] [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
>|||But, lest I forget, You would be much much better off using DateTime or
SmallDateTime as the datatype of that column (Date_Updated) in the first
place...
"Munch" wrote:

> 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_w] [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
>