Date Problem

[ganeshvkl]

hi,
i'm using vb.net 2003 and sql server 2000. i have created one table named
tblYear
create table tblYear
(
sntYearCode smallint,
datFrom datetime,
datTo datetime,
);
i inserted values below
insert into tblYear(sntYear Code,datFrom,da tTo) values (1,'2009-04-01','2010-03-31')
insert into tblYear(sntYear Code,datFrom,da tTo) values (2,'2010-04-01','2011-03-31')
insert into tblYear(sntYear Code,datFrom,da tTo) values (3,'2011-04-01','2012-03-31')
insert into tblYear(sntYear Code,datFrom,da tTo) values (4,'2012-04-01','2013-03-31')



select sntYearCode from tblYear where datfrom >= '2009-07-24 ' and
datto <= '2009-07-24'

if i execute the query , my Result should be 1 but i'm not getting..

it's urgent..
Thanx in Adv.

Ganesh

[MrMancunian]

I think it might have something to do with the fact that you created two datetime fields, but you're only checking the date. Another thing you can think of is the local datetime settings on both the server and your PC...

Do you get an error or doesn't it return a value?

Steven

[ganeshvkl]

hi steven,
thanx for u'r reply.
i'm not getting any return value.

ganesh

[MrMancunian]

Not getting a return value probably means that your query isn't correct. Try to change this line:

select sntYearCode from tblYear where datfrom >= '2009-07-24 ' and datto <= '2009-07-24'

to this:

select sntYearCode from tblYear where datfrom >= '2009-07-24 00:00:00' and datto <= '2009-07-24 23:59:59'

As you defined you tablecolumns as datetime, I can imagine that it want to have both date and time, instead of date only.

Steven

P.S. Did you copy your code directly from your project? If so, there is an empty space between the first 24 and the quote. You should fix that, as it is a string, and it won't be trimmed.

[aryanbs]

Your comparsion is wrong, should be this

select sntYearCode from tblYear where datfrom <= '2009-07-24' and
datto >= '2009-07-24'