Select records from a table monthwise

[ssreevidya.m]

Hai ,

I have a table Student_transport_Details having columns :

UserId
DesignatonId
StartDate
Enddate

I need to get records for a particular month.
Can i retrieve records by passing MonthId [between 1 -12].

Thanks in advance

[adam_k]

The function you need is month().

select * from Student_transport_Details 
where month(StartDate) = 1 
or month(StartDate) between 6 and 10
or month(StartDate) in (2,4)

[ssreevidya.m]

Hai ,
Thanks fro your replay.

But I just pass only one value for monthId and I want to retrieve records only if that monthId exist between startdate and enddate.

[adam_k]

That's not a problem. I was just showing you how you can use the function in case you need a range or a list or months.

you can use where @monthid between month(startdate) and month(enddate)

[ssreevidya.m]

Hai ,
I tried it and wored correctly , But again a problem occured.
If the start date and end date are like 04-11-2011 and 04-04-2012 , then the months are 11 and 4 .Then incorrect records get.

[urtrivedi]

I hope this query will work

select * from Student_transport_Details 
where month(StartDate) = @monthid 
or month(endDate) =@monthid
or ( convert(datetime, '01-'+cast(@monthid as varchar)+'-'+year(startdate), 105) between StartDate and enddate)
or ( convert(datetime, '01-'+cast(@monthid as varchar)+'-'+year(enddate), 105) between StartDate and enddate)

[adam_k]

@utrivedi: Be carefull when either working or b giving date queries with converts as no all regional settings have dd-mm-yyyy. The dates specified could easily be April 11th 2011 to April 4th 2012 or November 4th 2011 to April 4th 2012.
If you have to provide a conversion use DATEFORMAT.

@OP: I thought the requirement was to give only month as criteria. You can use year() function the same way as month() to add year in your where clause.

[ssreevidya.m]

Hai ,
I tried using the query you suggested , but I got some error As:

"Conversion failed when converting the varchar value '01-4-' to data type int."

[urtrivedi]

Post following 3 things here
1) sample data
2) paramter u want to pass (say monthid=5 or u want to pass from date to date range)
3) sample result you want from 1 and 2 above

[jcarbillon]

you need to consider not just months but year as well, you can get month and year from a date column using

month(datecolumn)
year(datecolumn)

by using "between" and these 2 code above you can get your problem solved, we may not know what exactly your problem is but we hope this will lead to the solution you need.

[red_ruewei]

you need to consider not just months but year as well, you can get month and year from a date column using

month(datecolumn) year(datecolumn)

by using "between" and these 2 code above you can get your problem solved, we may not know what exactly your problem is but we hope this will lead to the solution you need.

Hi, i have the same problem.
I need to generate report by using range of month and year. So it have month and year from and month and year to.

I'm stuck on how to SELECT rows In a Month Range. The month is take from date yyyy-mm-dd stored on database(tkh_masuk).

I manage to select record on particular month and year using this below example.;)

Select * from table where YEAR(date_data)='2011' AND MONTH(date_data)='01'

U have mention about using BETWEEN.
But have no idea how ;). I had try this but nothing come out. Sorry i new to this.:$

SELECT * FROM daftar_kes WHERE tkh_masuk BETWEEN (YEAR(tkh_masuk)='2011' AND MONTH(tkh_masuk)='09') AND (YEAR(tkh_masuk)='2011' AND MONTH(tkh_masuk)='11')

Thanks in advance for attention and kind.:pretty::icon_redface:

[jcarbillon]

ok so i guess you have 2 different parameters (september 2011) and (november 2011),

1. you have to get the last day of your second date, on this case (nov 30), by using
dateadd function

2. on the first date always make it 1st day of the month (sept 1)

3. now you have two variables needed for your query

here's a sample

month1 = 9
month2 = 11
year1 = 2011
year2 = 2011

lastday = dateadd("d",-1,dateadd("m",1,CDATE(month2&"/1/"&year2)))

date1 = month1 & '/1/' & year1
date2 = month2 & '/' & lastday & '/' & year2

    SELECT * FROM daftar_kes WHERE tkh_masuk BETWEEN date1 AND date2

[adam_k]

You are making this too complicated friend.
Just use:

select * from table 
where year(date_field) between @start_year and @end_year 
and month(date_field) between @start_month and @end_month

Sometimes you've got to read your own code after a year or 2, under pressure and figure out what the hell you were thinking. Keeping it simple and avoiding fancy and complicated code to achieve a simple result will make the difference.

[BitBlt]

You could take a (syntactically corrected ;) ) version of what @jcarbillon suggested. As mentioned before, you have to be careful about the international date formats.

If you don't want to do that, then @adam_k's solution won't work if your selected dates cross over a year boundary. You need to have a WAY more complicated where clause to cover boundary conditions.

Here's some sample code covering scenarios where you either have start/end dates, or start month/year and end month/year:

declare @start_date datetime
declare @end_date datetime
declare @start_year int
declare @start_month int
declare @end_year int
declare @end_month int

-- Just populating variables for boundary testing...use your own if you like
select @start_date = dateadd(m, 3, getdate()), @end_date = dateadd(m, 20, getdate())
select @start_year = year(@start_date), @start_month = month(@start_date)
select @end_year = year(@end_date), @end_month = month(@end_date)

-- Version using dates
select * from dbo.myDateTestingTable
where      -- cases where date is start year, and month is greater than start month 
    (   year(myTestDate) = year(@start_date) 
        and month(myTestDate) >= month(@start_date)
    )
    or     -- cases where date is past start year, but not in end year
    (   year(myTestDate) > year(@start_date)
        and year(myTestDate) < year(@end_date)
    )
    or     -- cases where date is end year, but less than end month
    (   year(myTestDate) = year(@end_Date)
        and month(myTestDate) <= month(@end_date)
    )
-- Version using separate month and year parameters
select * from dbo.myDateTestingTable
where      -- cases where date is start year, and month is greater than start month 
    (   year(myTestDate) = @start_year 
        and month(myTestDate) >= @start_month
    )
    or     -- cases where date is past start year, but not in end year
    (   year(myTestDate) > @start_year
        and year(myTestDate) < @end_year
    )
    or     -- cases where date is end year, but less than end month
    (   year(myTestDate) = @end_year
        and month(myTestDate) <= @end_month
    )

I know it's ugly, but it works. And it saves you from having to assemble dates and thereby avoids the whole international date format thing.

Oh, and here's some code to build a test scenario to prove it yourself:

create table dbo.myDateTestingTable
(
myId int identity(1, 1) not null,
myTestDate datetime not null
)

declare @myDate datetime
declare @i int
set @i = 0
select @myDate = getdate()

while @i < 100  -- just a little mini-randomizing for test dates.
begin   
    select @myDate = dateadd(d, @i, dateadd(m, 1, @myDate))
    insert into dbo.myDateTestingTable
    (myTestDate)
    values
    (@myDate)
    set @i = @i + 1
end

select * from dbo.myDateTestingTable

Sorry for the length of the post. Hope all this helps! Good luck!

[red_ruewei]

for jcarbillon, adam_k, and BitBlt
tq so much guy.....it work!! :)