Date Fields

Hi all, i would be really grateful if you could help with this one
I have an MS SQL table with amongst others 3 fields -
DatePosted
Dayspostedfor
ExpiryDate
The value inserted into DatePosted id GetDate()
The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
I'd like ExpiryDate to calculate the other two to create an expiry date -
for instance -
DatePosted 01/01/06
Dayspostedfor 7
ExpiryDate 08/01/06
Any ideas would be really gratefully recieved
thanksGTN170777 wrote:
> Hi all, i would be really grateful if you could help with this one
> I have an MS SQL table with amongst others 3 fields -
> DatePosted
> Dayspostedfor
> ExpiryDate
> The value inserted into DatePosted id GetDate()
> The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
> I'd like ExpiryDate to calculate the other two to create an expiry date -
> for instance -
> DatePosted 01/01/06
> Dayspostedfor 7
> ExpiryDate 08/01/06
> Any ideas would be really gratefully recieved
> thanks
Lookup the DATEADD function in Books Online.
Is ExpiryDate always supposed to be equal to DatePosted +
Dayspostedfor? If so, then one of those columns is redundant. You
should decide which two of the columns to keep and drop the other from
the table. That's because redundant data leads to errors,
inefficiencies and over-complexity. If you just want to show both
values on the screen or in a report that doesn't mean you need to hold
them both in the table. You can do the calculation easily whenever you
need to.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First of all, adopt a single naming convention, namely ISO-11179. Next,
drop the redundant column (columns are not fields!! Important: Columns
can have constraints; fields do not) Start using CURRENT_TIMESTAMP
instead of the proprietary getdate(). Learn to use ISO-8601 date
formats.
CREATE TABLE Postings
(post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
expiry_date DATETIME NOT NULL,
CHECK (post_date < expiry_date),
.);
date <<
Unh? If you already know the expiry date, why compute it? Also, we do
not do computations in an RDBMS. SQL is declarative, so you do such
things outside of the schema and store the data.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||ISO-11179 states you should use singular names and not plural.
You should call the table Posting and not Postings.
If you are going to follow a standard then don't invent things to support
your own style.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:eHU9ewgGGHA.2064@.TK2MSFTNGP09.phx.gbl...
> First of all, adopt a single naming convention, namely ISO-11179. Next,
> drop the redundant column (columns are not fields!! Important: Columns
> can have constraints; fields do not) Start using CURRENT_TIMESTAMP
> instead of the proprietary getdate(). Learn to use ISO-8601 date
> formats.
> CREATE TABLE Postings
> (post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> expiry_date DATETIME NOT NULL,
> CHECK (post_date < expiry_date),
> ..);
>
> date <<
> Unh? If you already know the expiry date, why compute it? Also, we do
> not do computations in an RDBMS. SQL is declarative, so you do such
> things outside of the schema and store the data.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> ISO-11179 states you should use singular names and not plural. <<
For scalar data elements, not for data structures. I prefer a
collective name over a plural, and a plural name over a singular name
(unless there really is only one row in the table). Thus
Employee = bad. unless you are a very small company :)
Employees = better, since it shows that this is a set of more than one
element
Personnel = best, since it names the collection as such and not as the
grouping of many indivduals|||Hi again, sorry about this, but i am really struggleing with this. I would
like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess th
e
other option would be to have two tables - adDatePosted value (default) =
(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
28 and the run a query like the following through Dreamweaver MX
WHERE adDatePosted + adExpiryDate >= GETDATE()
Would this work? and if so would i need to use 7 or d,7 (7 days) in the
adExpiryDate value?
Thank you
"--CELKO--" wrote:

> For scalar data elements, not for data structures. I prefer a
> collective name over a plural, and a plural name over a singular name
> (unless there really is only one row in the table). Thus
> Employee = bad. unless you are a very small company :)
> Employees = better, since it shows that this is a set of more than one
> element
> Personnel = best, since it names the collection as such and not as the
> grouping of many indivduals
>|||On Tue, 17 Jan 2006 13:16:06 -0800, GTN170777 wrote:

>Hi again, sorry about this, but i am really struggleing with this. I would
>like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess t
he
>other option would be to have two tables - adDatePosted value (default) =
>(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
>28 and the run a query like the following through Dreamweaver MX
>WHERE adDatePosted + adExpiryDate >= GETDATE()
>Would this work? and if so would i need to use 7 or d,7 (7 days) in the
>adExpiryDate value?
>Thank you
Hi GTN170777,
There are three possibilities.
One: have the columns DatePosted (datetime or smalldatetime) and
DaysPostedFor (int, smallint or tinyint) in the table; use the
expression
DATEADD(day, DaysPostedFor, DatePosted)
whenever you need the expiry date.
Two: have the columns DatePosted and ExpiryDate (both datetime or
smalldatetime) in the table; use the expression
DATEDIFF(day, DatePosted, ExpiryDate)
whenever you need the number of days it's posted for.
Three: have the columns DaysPostedFor (int, smallint or tinyint) and
ExpiryDate (datetime or smalldatetime) in the table; use the expression
DATEADD(day, -DaysPostedFor, ExpiryDate)
whenever you need the date posted.
In all cases, if you prefer not to use the same expression over and over
again, you can use a VIEW or a computed column in the table.
Example of VIEW:
CREATE VIEW MyView
AS
SELECT Some other columns,
DatePosted,
DaysPostedFor,
DATEADD(day, DaysPostedFor, DatePosted) AS ExpiryDate
FROM YourTable
Example of computed column:
CREATE TABLE YourTable
(Other columns,
DatePosted smalldatetime NOT NULL,
DaysPostedFor int NOT NULL,
ExpiryDate AS DATEADD(day, DaysPostedFor, DatePosted),
More columns or constraints
)
Hugo Kornelis, SQL Server MVP