Days in a month Function 2

[essa2000]

Hi All ;

I am looking for a function which can give me no. of days in a month. For example NoOfDays(Jan, 2004) it should give me 31. If I pass NoOfDays(Feb, 2004) it should returns 29 otherwise I should return 28.

I am wondering if someone knows about that function.

Thanks


Muhammad Essa Mughal
Software Engineer
iLogic Inc.

 

[MDXer]

How about DateDiff using the first day of your month with the first day of the following month.

Select DateDiff(dd,'1/1/2004','2/1/2004')

returns 31

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[essa2000]

Yeah , I worked for me , I also got the solution after positing it.

Thanks.


Muhammad Essa Mughal
Software Engineer
iLogic Inc.

 

[ESquared]

and you can make the second part be a DateAdd to get the next month.

 

[donutman]

This makes it easy to "get" the 2nd date. The DateAdd function has been nicely designed for it. I used an example that you would think would fail. You should be able to use any @YourDate.

Declare @YourDate datetime
Set @YourDate='2004-01-31'
Select DateDiff(d,@YourDate,DateAdd(m,1,@YourDate))

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Karl, your answer gives 29, but the date passed in is in January, which has 31 days.

 

[donutman]

Oh well, at least I was right about thinking it should fail!

 

[MDXer]

I think Karl was after this

Declare @YourDate datetime
Set @YourDate='1/31/04'
Select DateDiff(dd,DateAdd(dd,-(DatePart(dd,@YourDate)-1),@YourDate),@YourDate)

where you can have the code generate the first date of the month from the date passed in.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ESquared]

MDXer,

Your example gives the result 30. What month was that supposed to be for, exactly?

Here's code that works no matter what date is passed in. Value returned is the number of days in the month that date falls in:

Declare @Yourdate datetime
Set @YourDate = '1/14/04'
Select DateDiff(d,Convert(varchar(7),@YourDate,102)+'.01', DateAdd(m,1,Convert(varchar(7),@YourDate,102)+'.01'))

-- Convert(varchar(7),@YourDate,102) yields 'yyyy.mm'
-- So for clarity it could be:

Declare
   @Yourdate datetime,
   @MonthStart datetime
Set @YourDate = '1/14/04'
Set @MonthStart = Convert(varchar(7),@YourDate,102)+'.01'

Select DateDiff(d,@MonthStart, DateAdd(m,1,@MonthStart))

 

[donutman]

In a desperate attempt to redeem myself, I'll name that count in 5 functions (including implicit ones E[sup]2[/sup] ):

Declare @Yourdate datetime
Set @YourDate='2004-01-31'
Select DateDiff(d,@YourDate,DateAdd(m,1,@YourDate))
       +Day(@YourDate)-Day(DateAdd(m,1,@YourDate))

See what actually happened is my browser cut off the last two terms from my original post and I didn't notice it...NOT.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Nice faked recovery there. I'll have to try to remember the technique the next time I flub. Oh... there was one more line, yeah...

5!?!? Mine was 4 functions: DateDiff, Convert, DateAdd, Convert.

Your solution does work this time. You *know* I tested it, trying to break it. Hehe.

 

[donutman]

But you have 2 implicit varchar to date conversions.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Fine, fine, call them half a function each and we're even!

There really should be built-in system functions that truncate the time returning 12am, and truncate the day returning the 1st of the month. I wrote my own DateMonth and DateDay functions because I use them so often. And single-statement scalar functions aren't so bad to use because I think they are expanded into the query at compile time.

 

[donutman]

The FAQ I wrote about holidays was originally called FloorDate. But after I proved to myself the difference in performance of just 1 call within the Select list, I over-wrote the FAQ. There doesn't seem to be a way to delete an FAQ or have I missed it?
The trouble with a FloorDate function is that you are only making your query look "nice". That's not a compelling reason to use it. Also, you're likely going to have more than one in the query. (That's why you feel compelled to use it in the first place...am I right?) Although, I didn't check to see if two was twice as bad as one!
I agree there should be more system functions for dates. I wonder if there is a channel for funneling a Yukon wish list?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamonTerrell]

Try this:
You give it a year and a month and it tells you the number of days. The concept is add 1 month to the first day of the month then subtract a day. You then have the last day of the given month, just take the day field out of that day...

DECLARE @Y varchar(4)
DECLARE @M varchar(2)
SET @Y = '2004'
SET @M = '6'
SELECT DAY(DATEADD(d,-1,DATEADD(m,1,CONVERT(datetime,@Y + '-' + @M + '-01'))))