The Real Assessment DST Query

Backstory: I published a piece of sql that didn’t do what I said it would do. I published a retraction. John Porter of Blackboard gently pointed it out and said he’d help.

What I wanted to do: Since ND wasn’t in a position to apply the Hotfix (re-issued only 2 weeks ago) in time for Daylight Savings Time change this Sunday, I wanted to see what our jeopardy was and to contact Instructors individually to let them know of the hour shift in their intended start/end times.

Here is a piece of sql that at least lets you determine the # of assessments in your database with dates on March 9nd. You may want to run two versions, once with “starttime” and once with “endtime”. Assignments and other date related objects would have to have their own queries, but probably aren’t as big a deal to Instructors.

I have to thank John Porter for showing me the existence of the dbutil_pkg which translates the columns’ Unix -like date/time stamp (which in this database -Bb Vista 4.2.3- includes zeros padding it out). This package has supreme reusability potential!

Run this sql as the user who installed your Bb Vista db/app. As always, standard disclaimers apply. Use at your own risk. Yada Yada Yada.

select * from webct.assmt_setting where Substr(webct.dbutil_pkg.getLocalDateString(starttime),1,10) =’2008-03-09′

PS. I have not yet been able to find which table to join with the assmt_setting table to actually tell you which section the assessment is in!!! Anybody out there?

Advertisements

One response to “The Real Assessment DST Query

  1. In assmt_setting you have delivery_context_id which is the id in learning_context. Pair those and then the sourcedid.id for easy searching in the GUI.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s