Date Problem in query

[oops4me]

hello,
I have written following query
select * from Candidate_Mst where Cand_fname='dsdsdsdhh' and Cand_lname='dsssgfg' and cand_email1= 'sdsfg' and DOB=2005-10-05

please tell me how to take check the date field in Mssql server. Because if i ve written query

select * from Candidate_Mst where Cand_fname='dsdsdsdhh' and Cand_lname='dsssgfg' and cand_email1= 'sdsfg'

then it is giving me proper output.

give me solution of checking date
Ajay

 

[fredericofonseca]

is DOB a date field or a char/numeric field? That will affect how you can do things.


But try
DOB = '2005-10-05'

and/or look at the cast functions.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[oops4me]

thanx for heping me

for dob i ve take datatype as datetime.
i ve tried DOB = '2005-10-05' but it is giving me error as it is not a character datatype.
so please give me solution to check with datetime datatype.
Ajay

 

[fredericofonseca]

DOB = CONVERT(DATETIME, '2005-10-05', 20)

but if DOB has the time bit it may not work as expected.

convert(char(10),DOB,20) = convert(char(10),CONVERT(DATETIME, '2005-10-05', 20),20)

should always work

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

i ve tried DOB = '2005-10-05' but it is giving me error as it is not a character datatype[

yes, '2005-10-05' is a character string, but it is also a perfectly valid datetime string too


so if DOB is datetime, and '2005-10-05' is datetime, and you are still getting an error, would you please tell us what the error was

r937.com | rudy.ca

 

[SQLBill]

Since DOB = '2005-10-05' is a valid TSQL command and it's not working for you....I wonder ARE you using Microsoft SQL Server?

-SQLBill

Posting advice: FAQ481-4875

 

[oops4me]

thanx to all of you for helping me

yes i am using MSSQl server 2000. but when i am using '2005-10-05' then it doesn't show me any record. i am giving this query at MSSQL server query analyser. just tell me how i should give the query.

 

[SQLBill]

1. Is there a record that matches that date?

2. What error are you getting?

3. What results do you get with this:

select * 
from Candidate_Mst 
where Cand_fname='dsdsdsdhh' 
   and Cand_lname='dsssgfg' 
   and cand_email1= 'sdsfg' 
   and DOB >= '2005-10-05 00:00'
   and DOB <= '2005-10-06 00:00'

-SQLBill


Posting advice: FAQ481-4875

 

[oops4me]

thanx SQLBill

i have give complete value i.e. storing in that field,like
DOB >= '2005-10-05 00:00:00.000' then it is showing me the result.
now i got the solution.
thanx to all for helping me.