Click here to Skip to main content
15,905,233 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
I write the sql here:
declare @sql varchar(100)
set @sql='select * from student where  birthday='+'''1990-01-20 21:00:00.000'''

but it search nothing,and it has data in db.
chaau 23-Jan-13 21:46pm    
are you sure you have data with this datetime value? Can you post here some example from your student table?
PIEBALDconsult 23-Jan-13 21:55pm    
Look into BETWEEN. And I hope you're not storing the date as a string. But thanks for using a proper date format.

1 solution

This query will return the records with a birthday date of 9pm on 20th January 1990, pretty specific. Check whether your birthday field is storing the time component of the datetime values

Assumes time component and it will be defaulted to 00:00:00.000 so you get all the 20th records
SELECT * FROM student WHERE birthday BETWEEN '1990-01-20' AND '1990-01-21'

This drops the time component from the comparison
SELECT * FROM student WHERE CONVERT(DATE,birthday) = '1990-01-20'
Share this answer
zqliu 23-Jan-13 22:40pm    
It is true when run directly,but fail when run using declare,exec this type
Subrata.In 23-Jan-13 23:42pm    
If your birth day field storing date and time then you can try:

SELECT * FROM student WHERE birthday BETWEEN '"+FromDate.ToString("dd-MMM-yyyy")+"' AND '"+ToDate.ToString("dd-MMM-yyyy 23:59:59")+"'

Store birth day without time and fetch using "dd-MMM-yyyy" format.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900