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()
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.