October 4, 2006 at 9:36 am
Hi!
I need to obtain the semester from a date (like 2006-01-01), is there anyway to obtain it trough a function like datename , datepart ?
October 4, 2006 at 10:27 am
I would use a case statement as semesters tend to be variable based on the educational institution.
SELECT CASE
WHEN GETDATE() BETWEEN '2006-01-01' AND '2006-03-01' THEN 'Winter'
WHEN GETDATE() BETWEEN '2006-03-02' AND '2006-06-01' THEN 'Spring'
...
END AS SEMESTER
You might want to look into instead of using actual dates doing DATEPART(dy,...) for day of year... so the functionality stands up past 2006. Assuming you have static dates for semesters.
October 5, 2006 at 1:55 am
I'd create a table and use a between join like below
CREATE TABLE Semester
(
SemesterName varchar(50),
StartDate smalldatetime,
EndDate smalldatetime
)
INSERT INTO Semester VALUES ('2006 Winter', '2006-01-01', '2006-02-28 23:59')
INSERT INTO Semester VALUES ('2006 Spring', '2006-03-01', '2006-05-31 23:59')
INSERT INTO Semester VALUES ('2006 Summer', '2006-06-01', '2006-08-31 23:59')
INSERT INTO Semester VALUES ('2006 Fall', '2006-09-01', '2006-12-31 23:59')
DECLARE @InputDate smalldatetime
SET @InputDate = '2006-06-21'
SELECT*
FROMSemester
WHERE@InputDate BETWEEN StartDate AND EndDate
OR if you don't want a record for each semester of every year then create a generic one like below
CREATE TABLE Semester
(
SemesterName varchar(50),
StartMonth tinyint,
EndMonth tinyint
)
INSERT INTO Semester VALUES ('Winter', '1', '2')
INSERT INTO Semester VALUES ('Spring', '3', '5')
INSERT INTO Semester VALUES ('Summer', '6', '8')
INSERT INTO Semester VALUES ('Fall', '9', '12')
DECLARE @InputDate smalldatetime
SET @InputDate = '2008-1-21'
SELECT*
FROMSemester
WHEREDATEPART(month, @InputDate) BETWEEN StartMonth AND EndMonth
Don't know whether this is totally valid and the dates are probably wrong as I'm not familiar with semester dates (GB).
October 5, 2006 at 5:40 am
If you do use a Semester table, suggest that you have a final record where the semester name is something like 'update the semester function', to provide a clue for future system maintainers.
The start date would be just after the end of the last known semester, and the end date could be some time in the year 3000 (or as high as dates will go).
This kind of thing is always problematic - since semester dates are arbitrary and could be changed for any number of reasons, even if you have data or an algorithm that are good now you could wind up in trouble in the future.
It might be worthwhile to show current semester start and end dates somewhere in your interface, if applicable - that could prompt maintenance action when and if needed.
October 5, 2006 at 9:02 am
Consider using a user-defined function here. Inside the user-defined function, you could use hard-coded values or a table or any other algorithm you want. This allows your method of determining semester dates to change without changing all SQL that uses semesters.
CREATE
FUNCTION uf_GetSemester (@date DATETIME)
RETURNS
VARCHAR(6)
BEGIN
-- Use whatever logic you want in here
DECLARE @sem VARCHAR(6)
SELECT @sem = CASE
WHEN DATEPART(mm, @date) >= 8 THEN
'FALL'
WHEN DATEPART(mm, @date) <= 5 THEN
'WINTER'
ELSE
'SUMMER'
END
RETURN @sem
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply