Q

Dates in the past month

I'm having some problems with my SQL statement. I have a table called News that has a field called PostedO, which is in datetime format. I would like to select the News entries from the past month. Say today is December 24. I want to see the records from November 24 until today. I'm running SQL Server 2000. Thanks for any help you can give me.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close