select query

Question

Post reply

select query

znkin

Old Hand

Points: 382
More actions
May 25, 2005 at 6:53 am

#86876

Hi,
How do I get to know the current month.
Actually I want to retrieve some records from my database table whose transaction dates are for the current month
example
select * from transmast where (trans_date >= 1st day of the month) and (trans_date <= getdate())
that means if today is the currentdate(month is May 2005) I need all records whose trans_date >= 1st May 2005 and <= current datetime
Thanks a lot

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Declare @StartDate as datetime

set @StartDate = DATEADD(M, DATEDIFF(M, 0, getdate()), 0)

select * from dbo.transmast where trans_date between @StartDate and GetDate()

Jayesh Shah SSC Veteran Points: 282 More actions · Answer 2

try this one.

select * from transmast where month(trans_date) = month(getdate()) and

year(trans_date) = year(getdate())

Seems to be simple and you could also have a lot of ways to get this.

Regards

Jayesh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Hey znkin, just make sure you have an index on trans_date and test both execution plans... you'll see which query runs faster .

ron k SSCertifiable Points: 6103 More actions · Answer 4

As Remi has hinted, his solution will allow the use of an index that may exist on trans_date. Using a function on the column such as MONTH(trans_date) will not allow the use of an index on that column and will most likely force a table scan.