Requires Free Membership to View
On the surface, this looks like a simple problem. Here at Ask The Expert, we usually don't answer the really simple ones, but this question has a couple of nuances that are worth exploring.
Every database system supports date arithmetic, at least to some degree, although the method that each uses is different. Standard SQL provides for expressions such as:
select *
from News
where PostedOn
between CURRENT_DATE - INTERVAL 1 MONTH
and CURRENT_DATE
However, this doesn't help you in SQL Server, where you have to say:
select *
from News
where PostedOn
between DATEADD(m,-1,GETDATE())
and GETDATE()
Now, one of the problems here is that GETDATE() includes a time component. If it's 3:15 in the afternoon on December 24 when you run this, the query may not return the News item posted at 11:20 on the morning of November 24, assuming that the news item was created with a PostedOn datetime value that included 11:20 as the time component.
If the PostedOn datetime field does not contain a time component, then this can only have been accomplished at the time the news item was entered, by some means such as:
insert
into News
( PostedOn
, ...
)
values
( CAST(
CONVERT(CHAR(10),GETDATE(),120)
as DATETIME )
, ...
)
The above is one of several ways of entering a date value without a time component. In effect, this forces the time component to 00:00:00 (midnight). This is the recommended approach for storing dates where the time component does not matter. If the time component does not matter to the application (e.g. news stories published on a particular day), then entering a non-midnight time component, such as by using GETDATE() alone, is going to get you into trouble.
So, let's go back the the query. Assuming that the PostedOn datetime values all have a time component of midnight, we still have to make one more adjustment. GETDATE() does include a time component, so we need to strip that off for the lower end of the range, the calculation which subtracts one month, so that in effect we get the date one month ago with the time set back to midnight. It's okay to leave GETDATE() as is for the upper bound, i.e. for today; all of today's news items will be included because their times are midnight.
select *
from News
where PostedOn
between DATEADD(m,-1,
CAST(
CONVERT(CHAR(10),GETDATE(),120)
as DATETIME )
)
and GETDATE()
This was first published in December 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation