By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Date calculation in SQL 2005

P: 92
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
May 27 '08 #1
Share this Question
Share on Google+
12 Replies


Delerna
Expert 100+
P: 1,134
You can use in a stored procedure
something like
Expand|Select|Wrap|Line Numbers
  1. IF EXISTS(
  2.                   Select 1 FROM TableToCheck 
  3.                   WHERE FieldToCheck=@ValueCheckingFor
  4.                         OR OtherFieldToCheck=@ValueCheckingFor
  5.                )
  6. BEGIN
  7.               Code to perform if exists
  8. END
  9.  
May 28 '08 #2

P: 92
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
Expand|Select|Wrap|Line Numbers
  1. IF EXISTS(
  2.                   Select 1 FROM TableToCheck 
  3.                   WHERE FieldToCheck=@ValueCheckingFor
  4.                         OR OtherFieldToCheck=@ValueCheckingFor
  5.                )
  6. BEGIN
  7.               Code to perform if exists
  8. END
  9.  
May 30 '08 #3

code green
Expert 100+
P: 1,726
At first I thought this could be a date format issue but the format you are using looks good.
Expand|Select|Wrap|Line Numbers
  1. 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?
May 30 '08 #4

P: 92
At first I thought this could be a date format issue but the format you are using looks good.
Expand|Select|Wrap|Line Numbers
  1. 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.
May 30 '08 #5

code green
Expert 100+
P: 1,726
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
May 30 '08 #6

P: 92
Sorry i got that first query answer, which i posted before. But now i need to check Between Date.
Can u Suggent me query.
May 30 '08 #7

code green
Expert 100+
P: 1,726
There is nothing wrong with this
Expand|Select|Wrap|Line Numbers
  1. 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.
May 30 '08 #8

P: 92
There is nothing wrong with this
Expand|Select|Wrap|Line Numbers
  1. 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
May 30 '08 #9

code green
Expert 100+
P: 1,726
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
May 30 '08 #10

P: 92
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
May 31 '08 #11

code green
Expert 100+
P: 1,726
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.
Jun 2 '08 #12

P: 92
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.
Jun 4 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.