Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>

relationships, primary key sql2000 question.

Hello,

I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.

When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.

Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.

Thanks

Peter

If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.

If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.

|||

What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

|||

Thanks guys, working through links now.

Peter

sql

relationship inside the same table

i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)

relationship between tables

I need to create a relationship between 3 tables,say x,y,z. y and z being th
e
parent tables to x. there's an id field in all the 3 tables. i need to creat
e
a relationship between these 3 tables such that the id field in x is related
to either y or z. in other words, sometimes the id field in table y should
act as the primary key constraint and some other times the id field in table
z should act as the primary key constraint.
Is it possible? if so,how?
Thanks!"HP" <HP@.discussions.microsoft.com> wrote in message
news:C58E1008-C4C2-47A3-B6CE-396BFE08AE27@.microsoft.com...
>I need to create a relationship between 3 tables,say x,y,z. y and z being
>the
> parent tables to x. there's an id field in all the 3 tables. i need to
> create
> a relationship between these 3 tables such that the id field in x is
> related
> to either y or z. in other words, sometimes the id field in table y should
> act as the primary key constraint and some other times the id field in
> table
> z should act as the primary key constraint.
> Is it possible? if so,how?
> Thanks!
Gakkk!
While you could probably figure out a way to do this, why in the world do
you want to break normalization rules like this.
Bad design with tend to lead to poor performance and a host of
maintainability issues. Relational design and normalization is based on
proven mathematical formulas. Violate the formulas and you bring integrity
issues in to the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Seems like you are missing a table. Create a fourth table to act as the
parent of all the other three.
David Portas
SQL Server MVP
--|||No it is not. A foreign key references one other table. Any other method
for referencing other than DRI is a kluge.
You should have y,z, x1 and x2, where y is the parent of x1 and z is the
parent of x2.
This is not redundant data, by the way. The x's are separate entities,
each describing similar "child" data of separate "parent" entities.
HP wrote:

>I need to create a relationship between 3 tables,say x,y,z. y and z being t
he
>parent tables to x. there's an id field in all the 3 tables. i need to crea
te
>a relationship between these 3 tables such that the id field in x is relate
d
>to either y or z. in other words, sometimes the id field in table y should
>act as the primary key constraint and some other times the id field in tabl
e
>z should act as the primary key constraint.
>Is it possible? if so,how?
>Thanks!
>

Relating the field name use as data in other table

hi!

i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows

FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]

OrigTable

OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok

so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..

my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is

create procedure Definition
@.SearchKey int,
@.FieldTypeint
as
begin
DECLARE @.FieldName varchar(10),
@.FieldDesc varchar(30),
@.TextColor int,
@.mysql varchar(4000)
SET NOCOUNT ON

SELECT @.mysql = 'select o.origkey, '

DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @.ResultType

Open MyCursor
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.mysql = @.mysql + 'o.' + @.FieldName + ' as ' + @.FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
END

CLOSE MyCursor
DEALLOCATE MyCursor

select @.mysql = @.mysql + ' o.results from OrigTable o where
FieldType = ' + @.ResultType
execute(@.mysql)

end

the result is Ok as

OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok

But the problem is how to include the TxtColor or concatenate TxtColor with this output below

OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok

thanx,
mygt

Here it is – You need not to have cursor here.

Code Snippet

Create Table #definitiontable (

[FieldType] int ,

[FieldName1] Varchar(100) ,

[FieldDesc] Varchar(100) ,

[TxtColor] Varchar(100) ,

[FontStyle] Varchar(100)

);

Insert Into #definitiontable Values('8','FIELD1','ABC','#1111','[fsbold]');

Insert Into #definitiontable Values('8','FIELD2','CDE','#2222','[fsitalic]');

Insert Into #definitiontable Values('8','FIELD3','EFG','#3333','[fsbold]');

Create Table #origtable (

[OrigKey] int ,

[FieldType] int ,

[FIELD1] Varchar(100) ,

[FIELD2] Varchar(100) ,

[FIELD3] Varchar(100) ,

[Results] Varchar(100)

);

Insert Into #origtable Values('1','8','test1','test2','test3','ok');

Code Snippet

--For Manual Query

select

[origkey],

max(case when a.[fieldname1] = 'field1' then [field1] + '+' + [txtcolor] end) [ABC],

max(case when a.[fieldname1] = 'field2' then [field2] + '+' + [txtcolor] end) [CDE],

max(case when a.[fieldname1] = 'field3' then [field3] + '+' + [txtcolor] end) [EFG],

[results]

from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]

Code Snippet

--For Dynamic Column Names

Declare @.SQL as varchar(8000);

Declare @.PreparedColumn as Varchar(8000);

Declare @.Columns as Varchar(8000);

Set @.PreparedColumn = ',max(case when a.[fieldname1] = ''?1'' then [?1] + ''+''+ [txtcolor] end) [?2]'

Set @.Columns = '[origkey]'

Select @.Columns = @.Columns + Replace(Replace(@.PreparedColumn,'?1', [FieldName1]),'?2', [FieldDesc])

From #definitiontable

Set @.Columns = @.Columns + ',[results]'

Exec ('Select ' + @.Columns + ' from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]')