Days in Month

Question

Days in Month

Ten Centuries

Points: 1382

July 12, 2007 at 1:15 pm

I have an issue with a project where I need to drop the total number of days in a given month into a table in a select statement.

How do I do this?

Someone here gave me the following function:

CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )

RETURNS INT

AS

BEGIN

RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30

ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND

YEAR(@pDate) % 100 != 0) OR

(YEAR(@pDate) % 400  = 0)

THEN 29

ELSE 28

END

END

END

I have not used functions before so I am not sure how to plug this into my code,

I am so close to automating something that took hours to minutes, this is the final step.

Thank you in advance for any help,

Richard

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

This would be easier

RETURN

DATEPART(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, @pDate), -1))

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

SELECT *, dbo.ufn_GetDaysInMonth(ColX) FROM Table1

N 56°04'39.16"
E 12°55'05.25"

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 3

Avoid the overhead of a function.

DECLARE @dt datetime

SET @dt = '2007-02-01'

select

DAY(DATEADD(mm,DATEDIFF(mm,0,@dt)+1,0)-1)

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

And, just in case anyone is curious about how much the function will slow things down...

--===== Find a safe place to do this test using "real tables" because function can't ref temp tables

USE TempDB

GO

--===== Make sure all the objects we want to build, don't already exist

IF OBJECT_ID('dbo.ufn_GetDaysInMonth') IS NOT NULL

DROP FUNCTION dbo.ufn_GetDaysInMonth

IF OBJECT_ID('dbo.JBMTest') IS NOT NULL

DROP TABLE dbo.JBMTest

GO

--===== Create the function that Peter suggested

CREATE FUNCTION dbo.ufn_GetDaysInMonth

(@pDate DATETIME )

RETURNS INT

AS

BEGIN

RETURN DATEPART(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, @pDate), -1))

END

GO

--=================================================================================================

-- Create a fair amount of test data

--=================================================================================================

--===== Create and populate a 1,000,000 row test table.

-- Column RowNum has a range of 1 to 1,000,000 unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Takes about 9 seconds to execute.

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (RowNum)

GO

--=================================================================================================

-- Test the duration of using the function and using a formula directly.

--=================================================================================================

--===== Declare local variables

DECLARE @StartTime DATETIME --Holds start time of each section of code

DECLARE @Bitbucket INT --Holds result of each function or formula usage

--===== Demo how long it takes for the function

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.ufn_GetDaysInMonth(SomeDate)

FROM dbo.jbmTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Function duration (ms)'

--===== Demo how long it takes for the function AGAIN just to show that cache doesn't matter here

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.ufn_GetDaysInMonth(SomeDate)

FROM dbo.jbmTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Function duration (ms)'

--===== Demo how long it takes to use the formula directly

SET @StartTime = GETDATE()

SELECT @Bitbucket = DATEPART(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, SomeDate), -1))

FROM dbo.jbmTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' NON-Function duration (ms)'

--===== Demo how long it takes for the formula directly AGAIN just to show that cache doesn't matter here

SET @StartTime = GETDATE()

SELECT @Bitbucket = DATEPART(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, SomeDate), -1))

FROM dbo.jbmTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' NON-Function duration (ms)'

--===== Cleanup the mess we made in TempDB

IF OBJECT_ID('dbo.ufn_GetDaysInMonth') IS NOT NULL

DROP FUNCTION dbo.ufn_GetDaysInMonth

IF OBJECT_ID('dbo.JBMTest') IS NOT NULL

DROP TABLE dbo.JBMTest

(1000000 row(s) affected)

9936 Function duration (ms)

10000 Function duration (ms)

1906 NON-Function duration (ms)

1703 NON-Function duration (ms)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Well, for average size of report, say 1000 rows, it will speed up the process by... 8 ms?

Not sure anybody would appreciate this effort.

Unlike the possibility not to invent that tricky formula (or to search for it in *.sql files) when somebody (not that brilliant in SQL) needs to build another report.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 6

Heh... then there's that

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard Sabbara Ten Centuries Points: 1382 More actions · Answer 7

Thank you all, it works now and my bosses are happy, mainly becuase I display the results in Excel and I used 'pretty' colors. At least I learned a cool new way to do something.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

If you really want to impress them, create an "External Data Range" that queries the database using a well written view and set it up to automatically update every ten minutes or so...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)