JCapper Message Board

JCapper 101
-- SQL - Saturday Only

Home	Register Log In

SQL - Saturday Only

jeff
8/19/2012
9:48:39 AM

--quote:

"Jeff, Is there a way in sql to make the data window display results for Saturday only? Thanks.
"

--end quote

You can do it using the Weekday command.

On my machine, WEEKDAY([DATE]) returns the following values for each day of the week:

WEEKDAY([DATE]) = 1 -- Sunday
WEEKDAY([DATE]) = 2 -- Monday
WEEKDAY([DATE]) = 3 -- Tuesday
WEEKDAY([DATE]) = 4 -- Wednesday
WEEKDAY([DATE]) = 5 -- Thursday
WEEKDAY([DATE]) = 6 -- Friday
WEEKDAY([DATE]) = 7 -- Saturday

Note that there is a server setting in IIS for first day of the week. The above values are based on the settings on my machine where the setting has Sunday persisted as the first day of the week. The numeric values returned by Weekday([Date]) can vary from one machine to the next depending on the setting.

Note: By setting the Hide Individual Plays/Show Individual Plays drop down in the Data Window to make the Data Window display individual plays - you can test out whether or not 7 is Saturday on your machine. Just compare the dates of individual plays returned in the Data Window against a calendar. No need to change the IIS setting,. Instead, adjust your sql expression to return the desired day of week based on whatever your setting happens to be.

With that in mind, and assuming that 7 (based in my settings) is Saturday - the following sql expression will cause the Data Window to return results for UPR rank=1 horses for Saturdays only on my machine:


SELECT * FROM STARTERHISTORY
           WHERE RANKUPR=1 
           AND WEEKDAY([DATE]) = 7

What if I wanted weekends only? (Saturday and Sunday?)

The following sql expression should do the trick:


SELECT * FROM STARTERHISTORY
           WHERE RANKUPR=1 
           AND (WEEKDAY([DATE]) = 1 OR WEEKDAY([DATE]) = 7)

Note that DATE is both a valid sql command and a valid data field name in the starterhistory table. In the above sql expressions I have "wrapped" it in square brackets. This tells the database driver that I want it to handle the text between the brackets as a data field name in the table (in this case the [DATE] field) and not as a sql command.

-jp

.

JCapper Message Board

JCapper 101 -- SQL - Saturday Only

JCapper 101
-- SQL - Saturday Only