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
>

formatting numbers

I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53

Any help is appreciated - ABSQL Server does not format output. That is the job of your interface (Access ADP, in this case).

Set the format of your form control or report field to display as currency.|||you could something like SELECT CAST(3.5553 as decimal(10,2)) but this rounds up (3.56). I would handle this in your VBA with a formatcurrency('your variable here',2).

This might round too. Or it might just cut off your percision. I forget. Test.

Format date output

Hello,
I have a query that looks something like this:
select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
from dbo.charges
I need output to be in mm/dd/yyyy format without changing users date/time
settings. Service_date is of datetime data type.
Deki PAYou need to use the optional style parameter.
select convert(varchar(50),getdate(),101)
Your code will look something like

> select LEFT(CONVERT(varchar(50),dbo.Charges.service_date,101) + SPACE(10),
> 10) AS ImmunizationDate
> from dbo.charges
HTH. Ryan
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||You can use the Convert Function as in
SELECT CONVERT(VARCHAR,GETDATE(), 101)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||The best answer is to format the data on the client using whatever API you
have available.
Second best (if your UI is not possible to change) check the CONVERT
possibilities.
Third best, use datepart and string functions and roll your own:
select cast(datepart(m, getdate()) as varchar(2)) + '/' + cast(datepart(d,
getdate()) as varchar(2)) + '/' + right(cast(datepart(year, getdate()) as
char(4)),2)
Then you can produce any format you want. I didn't deal with single digit
months because you should be able to use:
select CONVERT (varchar(10),getdate(),1)
But if you need something like this, it can be handy to use datepart to
format a date
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Deki" <Deki@.discussions.microsoft.com> wrote in message
news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
> Hello,
> I have a query that looks something like this:
> select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate
> from dbo.charges
> I need output to be in mm/dd/yyyy format without changing users date/time
> settings. Service_date is of datetime data type.
>
> --
> Deki PA
>|||Thank you all!
--
Deki PA
"Louis Davidson" wrote:

> The best answer is to format the data on the client using whatever API you
> have available.
> Second best (if your UI is not possible to change) check the CONVERT
> possibilities.
> Third best, use datepart and string functions and roll your own:
> select cast(datepart(m, getdate()) as varchar(2)) + '/' + cast(datepart(d,
> getdate()) as varchar(2)) + '/' + right(cast(datepart(year, getdate()) as
> char(4)),2)
> Then you can produce any format you want. I didn't deal with single digit
> months because you should be able to use:
> select CONVERT (varchar(10),getdate(),1)
> But if you need something like this, it can be handy to use datepart to
> format a date
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Deki" <Deki@.discussions.microsoft.com> wrote in message
> news:1D07032A-EEB0-42BF-AA7D-CE683EAF1312@.microsoft.com...
>
>