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 |
|
niozero
Starting Member
3 Posts |
 Posted - 2011-03-31 : 10:10:50
|
| What would be the correct syntax to use in order to get databased on a previous day? Example: I want to see everyone that made a payment yesterday. My field is Payment date. Would it be something like(GetDate - 1))Im learning SQL like a mad man! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-31 : 10:12:31
|
[code]where [Payment Date] >= dateadd(day, datediff(day, 0, getdate()), -1)and [Payment Date] < dateadd(day, datediff(day, 0, getdate()), 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-03-31 : 17:00:06
|
| Stop usignthe old Sybase getdate(); we have the ANSI/ISO CURRENT_TIMESTAMP now. Yesterday can be computed with this:DATEADD(DAY, CAST(CURRENT_TIMESTAMP AS DATE), -1)I hope that you are using the DATE data type and not the old DATETIME.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-31 : 20:05:47
|
quote:
Originally posted by jcelko
Stop usignthe old Sybase getdate(); we have the ANSI/ISO CURRENT_TIMESTAMP now.
Joe, what is the advantage of using current_timestamp over getdate()? It doesn't seem like getdate() is slated for deprecation. Does it have any performance advantages? The thought that goes through my mind is that if I use getdate() it will work even in SQL 2000, so why not use it.quote:
Yesterday can be computed with this:DATEADD(DAY, CAST(CURRENT_TIMESTAMP AS DATE), -1)
Minor correction: DATEADD(DAY, -1, CAST(CURRENT_TIMESTAMP AS DATE))quote:
I hope that you are using the DATE data type and not the old DATETIME.
Given that a lot of production systems run SQL 2005 or older(*), and since this forum is not specific to SQL 2008, I can see the rationale for using datetime, perhaps even at the expense of slight performance penalty. But, if we know the target platform is SQL 2008, of course !!(*) There are even people running SQL 7. If you don't believe me, see here. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158599 (Don't let the subject line scare you off :) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-04-01 : 06:53:11
|
| <<I hope that you are using the DATE data type and not the old DATETIME.>>Until OP uses 2008 or later, datetime column is the only possibleMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Pull data based on previous day