select query using column no instead of column name

I have table like this:

StartDate  Day1Hours Day2Hours Day3Hours Day4Hours Day5Hours Day6Hou开发者_如何学Gors Day7Hours
1/17/2010     8          8          8        8          8         8         8      
1/24/2010     8          8          0        0          10        2         2

How can i fetch Hours between 1/18/2010 and 1/22/2010 .

Using a CROSS JOIN to multiple out to one row per day with appropriate hours for the row.

;WITH cteFixedSchema AS
(
   SELECT
      DATEADD(day, DayOffset, StartDate) AS ProperDate,
      CASE DayOffset
         WHEN 0 THEN Day1Hours
         WHEN 1 THEN Day2Hours
         WHEN 2 THEN Day3Hours
         WHEN 3 THEN Day4Hours
         WHEN 4 THEN Day5Hours
         WHEN 5 THEN Day6Hours
         WHEN 6 THEN Day7Hours
      END AS DayHours
   FROM
      MyTable M
      CROSS JOIN
      (SELECT 0 AS DayOffset UNION SELECT 1 UNION SELECT 2
         UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) foo
)
SELECT
    SUM(DayHours)
FROM
    cteFixedSchema
WHERE
    ProperDate BETWEEN '20110118' AND '20110122'

This assume StartDate is a valid date(time) data type with no extra conversions needed

Use a map int->String which maps the day number to the column name.

C# example:

public class Example
{
    private static Dictionary<int, string> columns;

    static Example()
    {
        columns.Add(1, "Day1Hours");
        columns.Add(2, "Day2Hours");
        columns.Add(3, "Day3Hours");
        columns.Add(4, "Day4Hours");
        columns.Add(5, "Day5Hours");
        columns.Add(6, "Day6Hours");
        columns.Add(7, "Day7Hours");
    }

    public void queryThirdDay(string startDate)
    {
        StringBuilder query = new StringBuilder("SELECT ");
        query.Append(columns[3]); // get third day
        query.Append(" FROM table WHERE StartDate = '");
        query.Append(startDate);
        query.Append("'");

        // query it
        // ...
    }
}

I agree with the comments about the schema. If for whatever reason this is out of your control you can unpivot to get it into this schema then select from that.

Declare @basedata table
(
StartDate datetime,
Day1Hours int,
Day2Hours int,
Day3Hours int,
Day4Hours int,
Day5Hours int,
Day6Hours int,
Day7Hours int
)
INSERT INTO @basedata
select '20100117',8,8,8,8,8,8,8 union all
select '20100124',8,8,0,0,10,2,2

DECLARE @Start DATETIME
DECLARE @End DATETIME

SET @Start = '20100118'
SET @End = '20100122'

SELECT DATEADD(DAY, SUBSTRING(d, 4, 1) - 1, StartDate),
       Hours
FROM   (SELECT *
        FROM   @basedata b
        WHERE  b.StartDate BETWEEN DATEADD(DAY, -7, @Start) AND
                                   DATEADD(DAY, 7, @End))
       filtered UNPIVOT (Hours FOR d IN 
    (Day1Hours, Day2Hours, Day3Hours, Day4Hours, Day5Hours, Day6Hours, Day7Hours) ) AS unpvt
WHERE  DATEADD(DAY, SUBSTRING(d, 4, 1) - 1, StartDate) BETWEEN @Start AND @End