Current month minus current day

Beginners question:

I am using a calendar table joined to my data table so that it will give me all the dates regardless wether there is data for that day or not. Now, I want it to give me all the data from the current month minus the current day. Right now I am using (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE())) but that also gives me data for the current day which I do not want.

Any way I can make this happen?

Thanks

Try:

(MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()) AND (DAY(c.dt) != DAY(GETDATE()))

Chris

|||

Thank you Chris.

That will indeed not give me the current day but it also does not show me the current day with a null value. I still need to see the current day just not with any data in it. I dont even know if it's possible.

|||

I am still not sure if I get the aim; is this way off?

declare @.calendar table
( dt datetime
)
insert into @.calendar
select convert(datetime, '1/31/7') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 29

declare @.dataTable table
( dataDt datetime,
dataValue integer
)

insert into @.dataTable
select convert(datetime, '2/1/7 7:00') + number,
number
from master.dbo.spt_values (nolock)
where name is null
and number <= 27

select convert (varchar(11), c.dt, 102) as [Date],
d.dataValue
from @.calendar c
left join @.dataTable d
on d.dataDt >= c.dt
and d.dataDt < c.dt + 1
and d.dataDt < convert (datetime, convert(varchar(10), getdate(), 101))
where c.dt >= convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01')
and c.dt < dateadd(mm,1, convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01'))

Date dataValue
-- --
2007.02.01 0
2007.02.02 1
2007.02.03 2
...
2007.02.20 19
2007.02.21 20
2007.02.22 NULL
2007.02.23 NULL
...

This select does not enclose the datetime columns within a function and therefore may be able to utilize a datetime index (if one is available). Enclosing c.dt within a function will inhibit to use any index that might be available on this column.

|||

OK, slight change of plan - you can do this if you include the DAY condition as part of a LEFT JOIN rather than in the WHERE clause.

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON c.dt = ot.dt AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

or alternatively if your dates include times other than 00:00:00.000 :

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON CONVERT(VARCHAR(11), c.dt, 102) = CONVERT(VARCHAR(11), ot.dt, 102) AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

I'm taking a bit of a guess as to how you're joining your tables, but hopefully this should be closer to what you need.

Chris

|||Awesome. Thank you very much Chris.

Current month minus current day

Beginners question:

I am using a calendar table joined to my data table so that it will give me all the dates regardless wether there is data for that day or not. Now, I want it to give me all the data from the current month minus the current day. Right now I am using (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE())) but that also gives me data for the current day which I do not want.

Any way I can make this happen?

Thanks

Try:

(MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()) AND (DAY(c.dt) != DAY(GETDATE()))

Chris

|||

Thank you Chris.

That will indeed not give me the current day but it also does not show me the current day with a null value. I still need to see the current day just not with any data in it. I dont even know if it's possible.

|||

I am still not sure if I get the aim; is this way off?

declare @.calendar table
( dt datetime
)
insert into @.calendar
select convert(datetime, '1/31/7') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 29

declare @.dataTable table
( dataDt datetime,
dataValue integer
)

insert into @.dataTable
select convert(datetime, '2/1/7 7:00') + number,
number
from master.dbo.spt_values (nolock)
where name is null
and number <= 27

select convert (varchar(11), c.dt, 102) as [Date],
d.dataValue
from @.calendar c
left join @.dataTable d
on d.dataDt >= c.dt
and d.dataDt < c.dt + 1
and d.dataDt < convert (datetime, convert(varchar(10), getdate(), 101))
where c.dt >= convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01')
and c.dt < dateadd(mm,1, convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01'))

Date dataValue
-- --
2007.02.01 0
2007.02.02 1
2007.02.03 2
...
2007.02.20 19
2007.02.21 20
2007.02.22 NULL
2007.02.23 NULL
...

This select does not enclose the datetime columns within a function and therefore may be able to utilize a datetime index (if one is available). Enclosing c.dt within a function will inhibit to use any index that might be available on this column.

|||

OK, slight change of plan - you can do this if you include the DAY condition as part of a LEFT JOIN rather than in the WHERE clause.

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON c.dt = ot.dt AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

or alternatively if your dates include times other than 00:00:00.000 :

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON CONVERT(VARCHAR(11), c.dt, 102) = CONVERT(VARCHAR(11), ot.dt, 102) AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

I'm taking a bit of a guess as to how you're joining your tables, but hopefully this should be closer to what you need.

Chris

|||Awesome. Thank you very much Chris.

Current month date query

Hi,

I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient)

What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.

Do you just need something like this:

SELECT *FROM EventTableWHERE EventStartDate <= @.EndOfMonthOR EventEndDate >= @.StartOfMonth

You'll need to pass in the Start and End of Month values, of course. HTH

|||

I assume that you would like to return all events which started or finished in selected month. Maybe you can use Query like this if year is not important.

DECLARE @.tcMonth int

SET @.tcMonth=10 -- events for October

Select *

from Events

where MONTH(StartDate)=@.tcMonth

OR MONTH(EndDate)=@.tcMonth

Thanks

JPazgier

|||

That's great.

I changed the Or to an And though. :-)

|||

If you change OR to AND in this query you will receive only events which started and ended in selected month, with OR you should receive also events which were started earlier and finished in selected month and events which were started in selected month and finished after it.

Thanks

JPazgier