| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2009-11-11 : 11:00:16
|
| Today is 11/11/2009.If I add code where orderdate = '11/11/2009'code is working.But, if I add code where orderdate = getdate()code is not working.How to fix it? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-11 : 11:12:52
|
| what datatype is orderdate . what is the format of the dates in orderdate . remember GETDATE() also includes hours, minutes, second etc as such 2009-11-11 08:12:06.317. but if orderdate is 2009-11-11 00:00:00.000 then your query will return nada<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-11 : 11:15:59
|
So I assume your column orderdate isn't a datetime type - it's varchar()?where orderdate = convert(varchar(10),getdate(),103)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-11-11 : 11:27:30
|
| It is datetime type |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-11 : 11:30:05
|
OK.If you make a select - is orderdate displayed with time part other than 00:00:00 000?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-11-11 : 11:31:05
|
| I got it:>= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-11 : 11:32:23
|
However, this should work:where orderdate >= dateadd(d,datediff(d,0,getdate()),0)and orderdate < dateadd(d,1,dateadd(d,datediff(d,0,getdate()),0))
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
knox203
Starting Member
31 Posts |
Posted - 2009-11-16 : 14:19:28
|
I've always thought this looks a little more clean:WHERE (orderdate BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, DATEADD(d, 1, GETDATE()), 101)) Many ways to do it; it's all about preference. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 14:33:47
|
| you might thiunk it looks a little cleaner, but it is significantly less efficient |
|
|
|
knox203
Starting Member
31 Posts |
Posted - 2009-11-16 : 16:23:23
|
| I learn something new everyday!Thanks for the heads up, russell. Would you say that "Sun Foster"'s last post is the more efficient way? I use this type of conditional string in a lot of my queries so I'd be very inclined to know the most efficient way.Thanks, - Adam |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-11-16 : 18:02:52
|
| FWIW, if it is true that the order date alone (time = 00:00:00.000) works correctly, then you don't need an upper and lower limit on today's time range.e.g., OrderDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 18:39:34
|
| actually converting to binary is the most efficient way, but it is rarely worth the tradeoff in readability. but yes, the dateadd/datediff method is more efficient than converting to character types. Itzik Ben-Gan has written extensively on the topic, with samples you can try out for yourself to prove it |
|
|
|
|
How to use getdate() for today?