Using Datepart

Question

Post reply

Using Datepart

Roger Abram

Hall of Fame

Points: 3683
More actions
October 29, 2008 at 7:00 am

#214638

How can I use datepart to select records between a certain date range without having to hardcode the year?
For example,
I want to
select id from transactions where trandate >= '1/1/2008' and trandate <= '3/31/2008'
I don't want to have to hardcode the year because the code will continue to be used year after year and don't want to have to update it for each year in the future.
Thank you.
RA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

So you want a generic query that returns the data from 1/1 to 3/31 of the current year?

If so here's one way to do it:

Declare @start_date datetime, @end_date datetime

Set @start_date = '1/1/' + Convert(Char(4), DatePart(year, getdate()))

Set @end_date = DateAdd(Month, 3, @start_date)

Select @start_date, @end_date

I'd also change the query a little to this:

select id from transactions where trandate >= @start_date and trandate < @end_date

Because SQL Server before 2008 always stored date and time, so <= 3/31/2008 is actually saying <= '3/31/2008 00:00' so anything done on '3/31/2008 00:00:01' or later would not be included in your query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

DATEPART Books Online

You can use YEAR(datefield) and MONTH(datefield) such as

SELECT id FROM transactions WHERE YEAR(trandate) = 2008 AND MONTH(trandate) BETWEEN 1 AND 3

YEAR is just DATEPART(year, ___), MONTH is DATEPART(month, ___)

Note that use of DATEPART/YEAR/MONTH or any function on the date fields may slow down the performance due to Table Scans (and not able to take advantage of the Indexes)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

Jerry Hung (10/29/2008)
DATEPART Books Online
You can use YEAR(datefield) and MONTH(datefield) such as
SELECT id FROM transactions WHERE YEAR(trandate) = 2008 AND MONTH(trandate) BETWEEN 1 AND 3
YEAR is just DATEPART(year, ___), MONTH is DATEPART(month, ___)
Note that use of DATEPART/YEAR/MONTH or any function on the date fields may slow down the performance due to Table Scans (and not able to take advantage of the Indexes)

Jerry, please don't recommend this approach. It will not allow for indexes to be used if they exist on the date column. Jack's approach is much better and allows for indexes to be used.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

If you'd always like it to return Jan - Mar of the CURRENT year, then you don't even need to know what the current year is...

SELECT ID

FROM dbo.Transactions

WHERE TranDate >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

AND TranDate < DATEADD(mm,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)