Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcmcom
Starting Member
9 Posts |
 Posted - 2007-11-23 : 12:22:02
|
| I have this SQL Statement select templatename from templates where datepart(day,displaydate) = datepart(day,getdate()) and datepart(month,displaydate) = datepart(month,getdate()) and datepart(year,displaydate) = datepart(year,getdate())Basically it selects a name where the "DisplayDate" field matches todays date. However since the display date contains the time in it as well i had to split it up using datepart. i am sure there is a more elegant way of doing this, but i dont know it. If anyone can provide a suggestion it would be greatly appreciated. TIA,mcm |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-11-23 : 12:27:59
|
| UseCAST(CONVERT(varchar(11),displaydate) AS datetime)=CAST(CONVERT(varchar(11),getdate()) AS datetime)This will strip off timepart from both sides. |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-23 : 12:29:42
|
There is a huge amount of discussion about this on these forums and in SQLTeam articles, but here is one popular wayselect templatename from templates where DATEADD(dd, DATEDIFF(dd,0,displaydate), 0) = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) |
|
|
|
mcmcom
Starting Member
9 Posts |
Posted - 2007-11-23 : 12:56:41
|
| thanks visakh16, your solution worked great!mcm |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-23 : 20:32:45
|
for performance, this will be better as applying function on the column will discourage the use of indexesSELECT templatename FROM templates WHERE displaydate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)AND displaydate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
More elegant date select