Be Careful That You Know What Getdate() Returnsby Les Smith |   |
Be careful when using getdate() in SQL Server as a Start or End date for a sql statement or Stored Procedure. This is fairly elementary for most developers, but could give you a problem if you are not aware that the getdate() function in SQL Server returns both the current date and time.
For example if you had a Where clause that went something like this:
| Where Date_Requested >= getdate() - 1 |
and you wanted records that were requested yesterday....
Since getdate() returns current date and time, depending on the time that the query is run, you probably will not get what you are expecting. What you probably wanted here was just yesterday's date and a time of 00:00:00, meaning that you wanted all records that were requested yesterday. Unfortunately, using just a call to getdate(), you won't get all the records.
There are several ways to correct this, and probably can be done easier than I will demonstrate below, but the following line does the trick.
| Where Date_Requested >= cast(convert(varchar(8),getdate()-1,1) as datetime) |
What I have done is to convert the DateTime value returned from getdate() to varchar and then cast it back to DateTime. The effect is to drop the time and reinsert it as 00:00:00. I am sure someone will tell me an easier way, but let's just don't get caught thinking we are getting all of the records, when we aren't.
If you have comments on any of my articles, or want to ask a question, contact me on my blog at Les's Blog.
EmoticonEmoticon