Help : Need a SQL query

Hi,

I want to find out no.of days b/n tow dates, but, I need it something different as below.

If Start Date is MM/DD/YYYY -- what ever the starting date it is, I need it from starting day of the month.

If End date is MM/DD/YYYY -- what ever the End date it is, I need it from End day of the month.

For Eg: if Start date is 03/10/2005 , but, I need to calculate from 03/01/2005

if End date is 01/24/2007, but, I need to calculate up to 01/31/2007

Any help would be appreciated.

Thanks

First day of month:

SELECTCONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(m, @.date)) +'/01/' +CONVERT(VARCHAR,DATEPART(yyyy, @.date)))

End of month:

SELECTDATEADD(d, -1,DATEADD(m, 1,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(m, @.date)) +'/01/' +CONVERT(VARCHAR,DATEPART(yyyy, @.date)))))

Basically, the end of month is start of month + 1 month then -1 day.

Modified code from http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-get-last-day-in-month.html

|||

Sorry,

I'm not clear. Can you please give me complete query to calculate no.of days?

|||

Days from start of month:

SELECT ABS(DATEDIFF(d, @.date,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(m, @.date)) +'/01/' +CONVERT(VARCHAR,DATEPART(yyyy, @.date)))))

Days to end of month:

SELECT ABS(DATEDIFF(d, @.date,DATEADD(d, -1,DATEADD(m, 1,CONVERT(DATETIME,CONVERT(VARCHAR,DATEPART(m, @.date)) +'/01/' +CONVERT(VARCHAR,DATEPART(yyyy, @.date)))))))

Phew! :-)