December 11, 2006 at 2:48 pm
query
SELECT count(Distinct PolicyNumber),CONVERT(varchar(4), EffectiveDate, 120)
from GE_Transaction
WHERE Status='A' AND DATEADD(year,10,EffectiveDate) > 'MonthReported' and Month_of_file='20060901'
GROUP BY CONVERT(varchar(4), EffectiveDate, 120)
Month Reported column has values like '09/01/06' for the month of september-06
what i want is to run the query in a way
where Month Reported column takes the ending date of the month as in query below and not the starting date of the month
query
SELECT count(Distinct PolicyNumber),CONVERT(varchar(4), EffectiveDate, 120)
from GE_Transaction
WHERE Status='A' AND DATEADD(year,10,EffectiveDate) > '09/30/06' and Month_of_file='20060901'
GROUP BY CONVERT(varchar(4), EffectiveDate, 120)
December 11, 2006 at 3:51 pm
DATEADD(year,10,EffectiveDate) > (DATEADD(mm, 1,MonthReported) - 1)
However, if you want it up to the second, use
CAST(convert(varchar(10),(DATEADD(mm, 1,MonthReported) - 1), 101) + ' 11:59:59 PM' as datetime)
December 11, 2006 at 4:29 pm
I would also suggest adding SET DATEFORMAT MDY to the start of your script. Otherwise, how will SQL Server know 09/01/06 is September 1st, 2006? It could be 9 January 2006.
-SQLBill
December 12, 2006 at 2:10 am
What is the datatype of the column MonthReported?
Is it actually a char that has literal values like '09/01/06' or is it a datetime and '09/01/06' only happens to be the display format you're watching?
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply