date problem

[venu656]

hi experts in my table i have a column with name Date and id Date id 2009-7-13 218665 2009-7-14 218665 2009-7-15 218665 2009-7-16 218665 2009-7-19 218665 2009-7-20 218665 2009-7-21 218665 i want to know weather rows r present from one date to another like from date and to date if i have give from date =2009-7-13 and to date = 2009-7-20 it shuld compare with the date column it should not show all the rows are present because 200-7-17 is not available.... if i have given from date = 2009-7-12 and to date= 2009-7-16 even it shuld not show because 2009-7-12 is not present and vice versa plz guide me

[dan sh]

How about this?

if(exists(select col1 from temptable where col1 = @fromDate or col1 = @toDate))
begin
select col1,col2 from tablename
where (col1 between @fromDate and @toDate)
end

else
begin
select 'Table does not contains one of the date.'
end

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[venu656]

no friend it was not working even the columns r getting which r out of dates

[dan sh]

Missed out the first part. Sorry. AFAIK you will need to write a while loop to do this matching all the dates between the range. But beware, from whatever I have read in the forums, looping techniques might cause performance related issues.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[venu656]

can u work out on that iam not getting good idea abt while loop

[dan sh]

venu656 wrote:

can u work out on that

Will I get paid for that? I guess no. Then I wont. Here are the links that will help you. While loop in SQL[^] DateAdd function in SQL[^]

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[venu656]

no yaar i cant pay iam still under bankrupt so let me describe that

[Luc Pattyn]

your database should store dates and datetimes as such, not as strings. stringed dates just beg for problems. :)

Luc Pattyn

:badger: :jig: :badger:

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

:jig: :badger: :jig:

[venu656]

thats not a big problem of string i want to know how to check each and every field in the date

[Luc Pattyn]

when you store dates as real dates, not strings, you could use date functions[^]; stored as strings, you can try and apply string substringing, however you could be subject of regional setting difference problems. :)

Luc Pattyn

:badger: :jig: :badger:

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

:jig: :badger: :jig:

[Henry Minute]

If there can only be one entry per date then you can compare the number of rows returned with the number of days between the two dates. Other than that I cannot help. :)

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[Lost User]

A date does not contain subfields. You're prolly referring to the parts called "day", "month" and "year", but it takes all three to create a date. A date is not internally stored as a combination of "day, month, year", but as a number, representing "days passed since ". The date that you "see" (the string) is a visual representation of the same fact, but formatted for your local culture. It's a visualisation of the date (the plain number in the database) in a specific, culture-dependant string. In other words; if it fits in a DateTime, then you won't have to check whether the month is larger than "12" :)

[venu656]

hai sir the date is one fiels but we are comparing here one fiels with fromdate and todate