Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hello friends
I am with task to make a time table.In that I want to assign teacher for particular class. In class registration i have registered a classes based on fromyear(2012) and Toyear(2013) as a academic year.my doubt is suppose i want to select classes in the academic year(2012-2013)based on the current year in sql queries then from janaury to march would get next year class details.how to avoid rectify this problem.
My stored procedure for getting class below. its work fine for upto december then from january it gives the next year class detail.
alter procedure sp_addstandered
as
begin
declare @fromyear int,@toyear int
set @fromyear=convert(int,datepart(year,getdate()))
set @toyear=@fromyear+1
select Standered_name from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)
end
Posted 21-Sep-12 0:10am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

instead of from year and to year use a function called DATEDIFF()
  Permalink  
Comments
baskaran chellasamy at 21-Sep-12 6:31am
   
but already i registered class with academic year. so i dont know how datediffer help me to give a last year details for(january to march of current year ) perod.
IdeasPravinh at 21-Sep-12 6:36am
   
if i have understood it correctly then you have registered classes with years not with months ri8?
baskaran chellasamy at 21-Sep-12 6:39am
   
yes. registered with year
baskaran chellasamy at 21-Sep-12 6:41am
   
This is my store procdure but it is an error on case statement.and also if last year not registered then i would not return correct year. please see this stored procedure and let me know to make any changes .
alter procedure sp_addstandered
as
begin
declare @fromyear int,@toyear int
declare @month varchar(30)
set @fromyear=CONVERT(int,DATEPART(YEAR,GETDATE()))
set @toyear=@fromyear + 1
case set @month= convert(varchar(30),datepart(month,getdate())) when @month='January' or 'Febraruy' or 'March' then @fromyear=@fromyear-1 end

select Standered_name from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)
end
IdeasPravinh at 21-Sep-12 6:53am
   
Sory dude, but the entire SP needs a revamp
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Use BETWEEN clause to add your date difference
  Permalink  
Comments
baskaran chellasamy at 21-Sep-12 6:47am
   
I just using year to registered a class. so how can differentiate this date differenece.
and modified stored procedure is below.it with error on case statement.and if last year not registered then it could not return any class.
<pre>alter procedure sp_addstandered
as
begin
declare @fromyear int,@toyear int
declare @month varchar(30)
set @fromyear=CONVERT(int,DATEPART(YEAR,GETDATE()))
set @toyear=@fromyear + 1
case set @month= convert(varchar(30),datepart(month,getdate())) when @month='January' or 'Febraruy' or 'March' then @fromyear=@fromyear-1 end

select Standered_name from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)
end</pre>

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 310
1 Sergey Alexandrovich Kryukov 166
2 PhilLenoir 164
3 Richard MacCutchan 160
4 Sharmanuj 146
0 Sergey Alexandrovich Kryukov 6,081
1 OriginalGriff 5,115
2 CPallini 2,473
3 Richard MacCutchan 1,597
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 21 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100