| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2006-07-18 : 06:15:21
|
| I have the following query and it should return a record. It works if I remove >= GETDATE() and have the criteria as =2, but if I add the date criteria it returns no records. The date in the table is 18/07/2006 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-18 : 06:17:51
|
| Possibility comparison condition is failing due to time part...use convert like below to truncate time part from date:convert(varchar(10),getdate(),101) = convert(varchar(10),DateCol,101)Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2006-07-18 : 06:24:34
|
| I found this which now returns my recorddateadd(day,datediff(day,0,GETDATE()), 0) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-21 : 11:46:51
|
quote:
Originally posted by harsh_athalye
Possibility comparison condition is failing due to time part...use convert like below to truncate time part from date:convert(varchar(10),getdate(),101) = convert(varchar(10),DateCol,101)Harsh AthalyeIndia."Nothing is Impossible"
To omit time part dont use convert function. Use DateAdd and DateDiffMadhivananFailing to plan is Planning to fail |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-21 : 11:58:13
|
quote:
Originally posted by madhivanan[brTo omit time part dont use convert function. Use DateAdd and DateDiffMadhivananFailing to plan is Planning to fail
Madhivanan, Just as a curiosity, I wanted to know whether there is any performance penalty associated with using convert() than datediff/dateadd() combination...b'coz at my previous assignment I have seen convert used in almost every case to omit time part ??Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-21 : 12:12:23
|
| Goto Script Library and look for MVJ's date related topics. You can find that convert becomes slow and ineffecient if there are thousands of rows. Also you need to convert it back to datetime to make it as DateMadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-21 : 15:34:57
|
quote:
Originally posted by harsh_athalye
quote:
Originally posted by madhivanan[brTo omit time part dont use convert function. Use DateAdd and DateDiffMadhivananFailing to plan is Planning to fail
Madhivanan, Just as a curiosity, I wanted to know whether there is any performance penalty associated with using convert() than datediff/dateadd() combination...b'coz at my previous assignment I have seen convert used in almost every case to omit time part ??Harsh AthalyeIndia."Nothing is Impossible"
I ran testing with a number of different methods of removing the time part of datetime where I converted a million datetimes. I found that datediff/dateadd() was much faster then any other method, and that using convert to a string and back to datetime was by far the slowest.CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-07-21 : 15:40:18
|
quote:
Originally posted by Pinto
I have the following query and it should return a record. It works if I remove >= GETDATE() and have the criteria as =2, but if I add the date criteria it returns no records. The date in the table is 18/07/2006
It would be nice if you posted the query...read the hint link in my sigBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Problems with Getdate()