Date calculation in SQL 2005

[Limno]

Hai,

Suggest me, how can i check whether From Date and To Date is stored already.
I want to update Values for particular period of date. if i allocate the Date for particular person. It should not accept value for that date, till that date is set free. it must show Data Already exist. It should check both From Date and To Date.

Suggest me with Query. if i extend the period of that period. they first entry must set to 0 and new entry must become 1. How can i do this

Thanks in Advance

[Delerna]

You can use in a stored procedure
something like

IF EXISTS(
                  Select 1 FROM TableToCheck 
                  WHERE FieldToCheck=@ValueCheckingFor
                        OR OtherFieldToCheck=@ValueCheckingFor
               )
BEGIN
              Code to perform if exists
END
 

[Limno]

Thanks for Replying me.

I m hving doubt on Date Checking. how can i use between Query in Sql 2005.
I want to check whether Item rate is allocate already between particular period of date. if itz allocated already. it should show msg Already Allocate.

I m using Query as.

select ItemRate from Item where FromDate between '2008-05-07 00:00:00.000' and '2008-05-14 00:00:00.000'

but it shows msg, if i mention that starting date. i need. between dates.

Plz Help me.

Thanks in Advance.

You can use in a stored procedure
something like

IF EXISTS(
                  Select 1 FROM TableToCheck 
                  WHERE FieldToCheck=@ValueCheckingFor
                        OR OtherFieldToCheck=@ValueCheckingFor
               )
BEGIN
              Code to perform if exists
END
 

[code green]

At first I thought this could be a date format issue but the format you are using looks good.

select ItemRate from Item where FromDate between '2008-05-07 00:00:00.000' and '2008-05-14 00:00:00.000'

Are you aware that the BETWEEN function is inclusive of the dates submitted?

[Limno]

At first I thought this could be a date format issue but the format you are using looks good.

select ItemRate from Item where FromDate between '2008-05-07 00:00:00.000' and '2008-05-14 00:00:00.000'

Are you aware that the BETWEEN function is inclusive of the dates submitted?

But this code is not working, wht i need the answer. i dont want to allocate item between this date if it allocated to someother item rate.

Suggest me with Query how i can do this.

[code green]

OK slow down.
I think we are misunderstanding your problem.
Your question was

how can i check whether From Date and To Date is stored already

Then repeated

I want to check whether Item rate is allocate already between particular period of date

.But now it sounds like you have an UPDATE problem

i dont want to allocate item between this date if it allocated to someother item rate

. I am afraid the following is not very helpful

But this code is not working

A table structure and your desired result would be helpful

[Limno]

Sorry i got that first query answer, which i posted before. But now i need to check Between Date.
Can u Suggent me query.

[code green]

There is nothing wrong with this

select ItemRate from Item where FromDate between '2008-05-07 00:00:00.000' and '2008-05-14 00:00:00.000'

So what do you mean by

But now i need to check Between Date.

[Limno]

There is nothing wrong with this

select ItemRate from Item where FromDate between '2008-05-07 00:00:00.000' and '2008-05-14 00:00:00.000'

So what do you mean by

Itz not working. Only if i give this date '2008-05-07 00:00:00.000', it shows msg as Already exist. i need between date. if i give '2008-05-08 00:00:00.000' to is it work correctly or not.

Plz reply me.

Thanks in Advance.
Limno

[code green]

Itz not working.

Please don't use this term. It is no help at all and very frustrating when people are trying to help.

Only if i give this date '2008-05-07 00:00:00.000',

Give this date where? Show the query.

i need between date. if i give '2008-05-08 00:00:00.000' to is it work correctly or not.

To use BETWEEN you need two dates.
First the start date and then the end date.
The dates are inclusive.
But I have already told you all of this.
If this query IS NOT WORKING you need to explain the result you expect.
At least show some code.
There may be a syntax error

[Limno]

Thank you for replying me.

I am hving column fields as

1. item_id
2. FromDate
3. ToDate
4. ItemName
5. Rate

Here my problem is, i want to allocate Date for Particular ItemName(Eg. Sugar) as from some date to some date. if i allocate rate for that same ItemName(Sugar), it should not accept its entry. it should display some error msg. for this how can i write sql query to solve this problem. I want to check both FromDate column and ToDate column date.

Please don't use this term. It is no help at all and very frustrating when people are trying to help.
Give this date where? Show the query.
To use BETWEEN you need two dates.
First the start date and then the end date.
The dates are inclusive.
But I have already told you all of this.
If this query IS NOT WORKING you need to explain the result you expect.
At least show some code.
There may be a syntax error

[code green]

You have been given a number of suggestions already and your reply has only been

It's not working

or

show me the query

Please show the query you have constructed so far then we can be sure of what you are trying to do.
Because I have no idea if you are attempting an UPDATE or an INSERT or a SELECT then dispaying the results via a program.

[Limno]

You have been given a number of suggestions already and your reply has only been or
Please show the query you have constructed so far then we can be sure of what you are trying to do.
Because I have no idea if you are attempting an UPDATE or an INSERT or a SELECT then dispaying the results via a program.

Thanks you for ur suggestions,

i found that my answer. I used the query by using where condition not by BETWEEN query.