Get values for every day in specific month (or between months)

Question

Possible Duplicate:
Get a list of dates between two dates

This is my sql:

select * from table as t 
where DateAndTime BETWEEN '2011-03-01' AND '2012-01-25'

Question: * For example, i need get data from January, 2012 for every day (except weekends).

How can i do it?

that is, you want to `GROUP BY` the day part of the DateAndTime field, ignoring the weekends? — vulkanino, Mar 05 '12 at 08:43

score 0 · Answer 1 · answered Mar 05 '12 at 08:45

0

try this.

SELECT * 
from table as t 
where (DateAndTime BETWEEN '2011-03-01' AND '2012-01-25') AND
       DateAndTime NOT IN ('2011-03-03', '2011-03-10') -- Put the excempted
                                                       -- dates here

answered Mar 05 '12 at 08:45

John Woo

258,903
69
498
492

does BETWEEN work with strings? – vulkanino Mar 05 '12 at 08:49
you can use `DATE` function. FOr example, `DATE('2011-03-03')` – John Woo Mar 05 '12 at 08:50

score 0 · Answer 2 · answered Mar 05 '12 at 08:50

select * from table as t 
where DateAndTime BETWEEN '2011-03-01' AND '2012-01-25' 
AND (DATEPART(weekday, DateAndTime ) <> 1 and  DATEPART(weekday,DateAndTime ) <> 6)

(DATEPART(weekday, DateAndTime ) = 1 is sunday (DATEPART(weekday, DateAndTime ) = 6 is saturday

vulkanino · Accepted Answer · 2012-03-05T09:05:30.957

0

SELECT
    * 
FROM
    yourtable AS T 
WHERE 
    yourdatetimefield BETWEEN STR_TO_DATE('2012-01-01', '%Y-%m-%d') AND 
                              STR_TO_DATE('2012-12-31', '%Y-%m-%d')
AND 
    DAYOFWEEK(yourdatetimefield) <> 1 AND    '  Sunday
    DAYOFWEEK(yourdatetimefield) <> 7        '  Saturday 
GROUP BY
    DAY(yourdatetimefield);

edited Mar 05 '12 at 09:05

answered Mar 05 '12 at 08:56

vulkanino

9,074
7
44
71

score 0 · Answer 4 · answered Mar 05 '12 at 09:52

0

try this query

SELECT * FROM tableName
WHERE DateAndTime BETWEEN '2011-03-01' AND '2012-01-25'
AND WEEKDAY(DateAndTime) <= 4

answered Mar 05 '12 at 09:52

Naveen Kumar

4,543
1
18
36

Get values for every day in specific month (or between months)

4 Answers4