Using DATEPART - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Using DATEPART

Author

Topic

aturner
Starting Member

29 Posts

Posted - 2004-08-02 : 10:32:31

I need to create a table and select statement that will allow me to post questions based upon the week within each month. The test is given quarterly. So, for example, the 1st quarter would begin in the month of August, were the first week begins on 2nd through 6th; the second week, 9th through 13th; the third, 16th through 20th, etc. This process would continue each year providing the user with a new set of questions each quarter.

I'm not sure how to use DATEPART within a SELECT/INSERT/UPDATE statement that will allow me to pull the correct question based upon month, week and year.

The initial table would have the following fields:

QuestionID int,
Topic varchar,
Scenario varchar,
Question varchar,
Answer varchar,
Discussion text

Do I need to add a datetime field in this table and set the Default Value to (getDate())? Or, create a separate table that has a range of values that returns 1 to 53 with the default value set to (getDate()) and add a foreign key from the second table to my initial table?

Help!!!

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-02 : 11:06:06

If this table returns questions based on which week it is, then you need a WeekNumber field (int) that can contain values 1 through 53. Then you can record the week number for the question against the question.

Then, when you need to extract a question for the week of the current year, use


SELECT ....
WHERE WeekNumber=DATEDIFF(week,'01 aug 2004',getdate())+1

I'd suggest parameterising the starting point (01 aug 2004 in this case) so that you don't have to change your SQL every year!

aturner
Starting Member

29 Posts

Posted - 2004-08-02 : 11:13:41

Do I include the weeknumber field in my initial table? If so, do I add the (getDate()) field to the default value field.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-02 : 11:20:19

WeekNumber should be in your Initial table.

Why would you add GETDATE() (which returns a DATE) to a field that contains a week number ?

I read that you were trying to store questions and relate them to a particular week, therefore you store the Week Number that the question relates to (e.g. put 1 for all questions for week 1, 2 for week 2, and so on).
Then, using the SELECT format I described, you can retrieve questions based on week number (e.g. today (2nd August) would return 1 from the DATEDIFF expression.).

aturner
Starting Member

29 Posts

Posted - 2004-08-02 : 11:42:25

In some of the examples I came across reference DATAPART, I noticed users where adding the (getDate()) to the Default Value section. So, that's why I posed the question on whether or not I needed to add this to my table.

Here's another idiotic questions. Is the WeekNumber (int) automatically generated like the QuestionID field? Or, do I leave this field blank and it will interpret what is required based upon the SELECT statement you indicated.

Another curve, what if the week does not have a question to post? How do I make that determination in the SELECT statement or to check for null values?

aturner
Starting Member

29 Posts

Posted - 2004-08-02 : 12:33:17

I used the SELECT example as shown below. However, I get an invalid column name 'WorkNumber'.

SELECT
QuestionID,
WorkNumber,
Topic,
Scenario,
Question,
Answer,
Discussion
FROM RollCall WHERE WeekNumber=DATEDIFF(week, '01 Jul 2004', getDate()) +1

What am I doing wrong? I entered the WorkNumber field as an (int) in my field.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-03 : 03:32:21

quote:

Is the WeekNumber (int) automatically generated like the QuestionID field?

I'd say not, but it depends on what your requirements are. I would populate this at the time I populate the question record so that I could control in which week the question would be set.

quote:

Or, do I leave this field blank and it will interpret what is required based upon the SELECT statement you indicated.

Err, no. Leave it blank and it will be blank. Therefore the question won't be linked to a week...

quote:

What am I doing wrong? I entered the WorkNumber field as an (int) in my field.

Probably a typo between your table definition for RollCall and your SELECT statement. Post the CREATE TABLE statement for your rollcall table.
Also, detail a little about what this table is used for. e.g. Is it used for storing the questions you are going to set, or to store answers given by people ? Having a bit more information about schema and requirements will help give you the best solution.

aturner
Starting Member

29 Posts

Posted - 2004-08-03 : 12:06:00

Here's the example of the CREATE TABLE:

CREATE TABLE [dbo].[RollCall] (
[TrimesterID] [int] IDENTITY (1, 1) NOT NULL ,
[Quarters] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Topic] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Scenario] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Question] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Discussion] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The purpose of this table is to display a topic, a scenario, a question regarding the scenario and answer, along with discussion texts. Each week a different topic is discussed during morning meets to enhance employees knowledge about certain changes effecting them in the workforce. A user will access this through a web browser. When they come into the web page, I want them to be able to first select the week, that will redirect them to the page listing the topic for that week, the scenario and question(s). There will be a button that the user will be required to press in order to obtain the answer and discussion.

I want to be able to write a stored procedure that will pull the TrimesterID that represents the 1 week. I added the Quarters field to allow the week, for example, August 2nd - Auguest 6th to be displayed to the user. Since, I tried to enter each topic using the below statement was entering the same date and not the week the actual question should display. For example, week one should display again Aug 2 - 6; week two, Aug 9 - 13, week three, Aug 16 -17 when I enter the topic, scenario, question and discussion.

SELECT
QuestionID,
WorkNumber,
Topic,
Scenario,
Question,
Answer,
Discussion
FROM RollCall WHERE WeekNumber=DATEDIFF(week, '01 Jul 2004', getDate()) +1

I hope this explains what my intend is.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-04 : 04:41:45

OK, I think I understand what you are saying.
I think your problem is that you are changing the date in the WHERE clause, am I correct ?


FROM RollCall WHERE WeekNumber=DATEDIFF(week, '01 Jul 2004', getDate()) +1

In your previous post, you stated that WEEK 1 should begin 2nd August, therefore the date in the above query should be '02 Aug 2004', and never change, unless the date representing WEEK1 changes, does that make sense ?
Then the code works out how many weeks between today (GETDATE()) and the starting date ('02 aug 2004'), and returns results for the matching week number.
If you want the user to select a week number then simply change that line to

FROM RollCall WHERE WeekNumber=@ChosenWeek

Where @ChosenWeek represents the Week number selected.
If you want the user to enter a date, then the code should be

FROM RollCall WHERE WeekNumber=DATEDIFF(week, '01 Jul 2004', @ChosenDate) +1

I hope that makes sense and answers your query.

aturner
Starting Member

29 Posts

Posted - 2004-08-04 : 07:45:49

It does explain it a little easier. However, based upon the table I created as follows:

CREATE TABLE [dbo].[RollCall] (
[TrimesterID] NOT NULL ,
[Quarters] [varchar] (25) AS NULL ,
[Topic] [varchar] (35) AS NULL ,
[Scenario] [text] AS NULL ,
[Question] [text] AS NULL ,
[Answer] [text] AS NULL ,
[Discussion] [text] AS NULL
)

If I use the second example of "FROM RollCall WHERE WeekNumber=@ChosenWeek", do I create stored procedure and/or view that declares the parameter @ChosenWeek?

CREATE TABLE [dbo].[RollCall]
@ChoosenWeek
AS
(
SELECT
TrimesterID,
Quarters,
Topic,
Scenario,
Question,
Answer,
Discussion
FROM RollCall WHERE WeekNumber=@ChosenWeek

The TrimesterID is a autonumber (i.e., 1, 2, 3) and the Quarters field basically shows the week of Aug 2 - 6, Aug 9 - 10. So, which field am I basing the @ChosenWeek off of? Or, is the user entering a parameter, such as 1 to indicate Aug 2 - 6?

Let's say, the week begins June 1 through June 4, then should the statement read: FROM RollCall WHERE WeekNumber=DATEDIFF(week, '01 June 2004', getDate()) +1, even though, the month may begin in the middle of the week.

What I would like the SELECT statement to provide, if for example, I create a web page that when the user opens the page, they are provided with a drop down list box that displays Aug 2 - 6 and when they select this it then displays the Topic, Scenario, Question, Answer, and Discussion. Hopefully, this explains what my intend is.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-05 : 06:45:45

OK,
Assumption 1 - WeekNumber 1 is ALWAYS first week in August
Assumption 2 - User selects Aug 2-6 from a drop down list
Assumption 3 - Client application translates the 'Aug 2-6' into something meaningful (i.e. not presentation layer related), e.g. the week number.

So, your call to the stored proc may look something like...


Command.CommandText = 'up_GetQuestion ' & intWeekNumber

Your stored proc may look something like


CREATE PROCEDURE up_GetQuestion (@ChosenWeek INT)
AS
SELECT Question,Answer,Discussion
FROM RollCall
WHERE WeekNumber=@ChosenWeek

Your create table in the previous example is incorrect syntax.
You need to sit down and clearly think out your requirements before implementing the solution, as reading the above, I think you're still undecided about how you want the application to behave.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources