Urgent Help with datetime

emeka · March 1st, 2007, 04:50 AM

I am writing a code that will generate the last months data on every first day of a new month. The code will be run as a job. The code works very well. The code snippet is below.

select *
from emeka
where startdate >= Convert(varchar,dateadd(mm, -1,getdate()), 103)
AND startdate <= convert(datetime,dateadd(dd, -1, getdate()),103)

My problem is, how can i re-write code so that i will generate last months data regardless of the day of the new month. I have tried this code below but it is not working. I keep geting an error that:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Declare @month int
Declare @day int
Declare @startdate datetime
select @startdate = (select convert(varchar,startdate,103) from emeka)

select @startdate
select @month = datediff(month, @startdate, getdate())
select @day = datediff(day, @startdate, getdate())
--select @month
--select @day

select *
from emeka
where startdate >= Convert(varchar,dateadd(mm, -@month,getdate()), 103)
AND startdate <= convert(datetime,dateadd(dd, -@day, getdate()),103)

happygv · March 1st, 2007, 01:04 PM

Hi,

Try this, this should be what you are looking.

Code:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = Convert(DateTime, Cast(Year(DateAdd(m, -1, GetDate())) as Varchar(4)) + '-' + Cast(Month(DateAdd(m, -1, GetDate())) as Varchar(2)) + '-01')
SET @EndDate = DateAdd(m, 1, @StartDate)
SET @EndDate = DateAdd(ms, -3, @EndDate)
Select @StartDate, @EndDate

Cheers.

_________________________
- Vijay G
Strive for Perfection

emeka · March 1st, 2007, 04:39 PM

Thanks Vijay. The code works perfectly well.