Anybody know how to calc the number of days in a month, given a date ??

I can do it in Access/vb easily, but can't figure out how to do it in TSQL.

Forgive me if it's too easy - I'm new here...

Thanks

Darren

Anybody know how to calc the number of days in a month, given a date ??

I can do it in Access/vb easily, but can't figure out how to do it in TSQL.

Forgive me if it's too easy - I'm new here...

Thanks

Darren

Darren

Look at this one

select day(dateadd(m,1,getdate())-day(dateadd(m,1,getdate())))

Quote:> Anybody know how to calc the number of days in a month, given a date ??

> I can do it in Access/vb easily, but can't figure out how to do it in

TSQL.

> Forgive me if it's too easy - I'm new here...

> Thanks

> Darren

I had to think about this one for a while. In pseudo-English(? ;-)

instead of SQL you are saying

"What day of the month is (

The current date/time plus 1 month

minus

What day of the month is

The current date/time plus 1 month

)"

That is, today being 24/10/2002 in British, you are taking

24/11/2002 and then subtracting 24 to get the last date this

month, and then taking the day-of-month of that. And it

doesn't matter that DATEADD(m,1,'2002-10-31') is '2002-11-30'

because in that case you get the same difference each time as

you subtract 30 from '2002-11-30'. (Obviously it /would/

matter if DATEADD(m,1,'2002-10-31') were '2002-12-01',

but it isn't.)

Having said that, how do we feel about

SELECT CONVERT( int, DATEADD(m,1,GETDATE()) - GETDATE() ) ? ;-)

(Oh, and what if you start to execute either one at

'2002-10-31 23:59:59.99'... ;-)

> Darren

> Look at this one

> select day(dateadd(m,1,getdate())-day(dateadd(m,1,getdate())))

> > Anybody know how to calc the number of days in a month, given a date ??

> > I can do it in Access/vb easily, but can't figure out how to do it in

> TSQL.

> > Forgive me if it's too easy - I'm new here...

> > Thanks

> > Darren

1. Last Day of Month, End of Month, First Day of Month

I searched all over google for answers to this question and came across many

solutions. But converting dates to character and plugging a "01" in and

putting the date back together seemed to old school.

Ex..

Or maybe it had a different purpose that I didn't catch in the thread. But

tinkering with Healthcare data has caused me to tinker with dates more than

I could care for.

I figure I would just post this incase someone else needed a simple solution

that didn't eat up the runtime of the query.

/*CurrentMonth is set to the first day of the month*/

/*Figure out the last day of the current month */

/*Figure out the last day of the previous month */

Not to mention storing all of these little bits of code in the newsgroup

allows me to find them in the future from anywhere.

2. Universal server with verity datablade

3. How to calculate last day of month in a view

4. A question about class "CRecordSet" in MFC,who can help me?

5. Calculating Number of Days and Number of Months between 2 dates

6. EDatabase Error Initializing Borland Database Engine

7. Calculating days in a month

9. Outputting days of the month for each month

10. Calculated Member for Getting Current Month and Previous Year Same Month

11. SQL Server Question: No. of days between 2 dates

12. How do I obtain NO. of transactions per day(SQL 6.5)

13. Scalar Function for Day Where day is 7:01pm one day to 7:00pm the next day

4 post • Page:**1** of **1**