Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I write the sql here:
declare @sql varchar(100)
set @sql='select * from student where  birthday='+'''1990-01-20 21:00:00.000'''
exec(@sql)
but it search nothing,and it has data in db.
Posted 23-Jan-13 15:27pm
zqliu359
Comments
Andrew Cherednik at 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 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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'
  Permalink  
Comments
zqliu at 23-Jan-13 22:40pm
   
It is true when run directly,but fail when run using declare,exec this type
_Subrata_ at 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)

  Print Answers RSS
0 OriginalGriff 280
1 Sergey Alexandrovich Kryukov 279
2 CPallini 205
3 Maciej Los 197
4 Afzaal Ahmad Zeeshan 160
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100