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.