Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

Question

I need to write an SQL Function (UDF) to find the First Day (int) and the Last Day (int) when I specify a date.

For example: When I specify (20130217) (i.e. Feb 17th, 2013), I should be able to find the First Day (01) and the Last Day (28) for the specified month (in this case, Feb).

How can I do that? Please provide the necessary code to implement this feature in my SQL Function.

Thanks.

Answer 1

declare @date datetime = '20120211'
----First Day of Month
SELECT DATEADD(mm,DATEDIFF(mm,0,@date),0) 'First Day of Month'
----Last Day of Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@date)+1,0))) 'Last Day of Month'

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 2

DECLARE @mydate DATETIME
SELECT @mydate = '20120211'
SELECT DATEADD(dd,-(DAY(@mydate)-1),@mydate) AS 'first day of the month'

SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, @mydate))),DATEADD(MONTH, 1, @mydate)) as 'Last Day of the Month'
            

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 3

Try

1st day: convert(datetime,substring('20130217',1,6)+'01',112)

last day: dateadd(dd,-1,convert(datetime,substring(convert(varchar,dateadd(mm,1,convert(datetime,'20130217',112)),112),1,6)+'01',112))

---

Many Thanks & Best Regards, Hua Min

Answer 4

there are some udf avaible on web...

create these two udf's .. http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

http://www.sql-server-helper.com/functions/get-last-day-of-month.aspx

after creating those udf's you can run like below

select day(dbo.ufn_GetLastDayOfMonth('01/15/2012')),day(dbo.ufn_GetFirstDayOfMonth('01/15/2012'))

---

Hope it Helps!!

Answer 5

Hi,

I have created below function. You can apply similar apporach to find First Day of Month or Last Day of Previous Month etc if you require.

CREATE FUNCTION dbo.GetLastDayOfMonth

(@iDate DATETIME)

RETURNS VARCHAR(25)

AS

       BEGIN

              DECLARE @tLastDay VARCHAR(25)

                  SELECT @tLastDay = CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,1,@iDate))),DATEADD(MM,1,@iDate)),101)

              RETURN @tLastDay

       END

--Test it

       SELECT dbo.GetLastDayOfMonth('20130217')

Answer 6

declare @dt date='20132117'

--SQL Server 2012
SELECT EOMONTH(@dt) -- end of the month
SELECT Dateadd(month,-1, Dateadd( Day,1,EOMONTH(@dt))) -- Start of the month

Answer 7

>>  need to write an SQL Function (UDF) to find the First Day INTEGER) and the Last Day INTEGER) when I specify a date.<<

NO! You need to learn how to program SQL. This is a declarative, set-oriented data language but you want to write it like 1960's BASIC. 

>> For example: When I specify (20130217) (i.e. Feb 17th, 2013), <<

An SQL programmer would know to use ISO-8601 and use '2013-02-17' with the dashes as required by ANSI/ISO Standards. 

>> How can I do that? Please provide the necessary code to implement this feature in my SQL Function. <<

We do not use functions in good SQL; that was BASIC, Fortran, COBOL etc. Yes, I know that you are comfortable with this model of programming, but it is not really SQL. 

We use a Calendar table (Google it). But other bad programers will give you proprietary kludges so you can keep being a bad SQL programmer. 

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.

CREATE TABLE Months
(month_name CHAR(10) NOT NULL PRIMARY KEY
   CHECK (month_name LIKE '[12][0-9][0-9][0-9]-[0-1][0-9]-00')
 month_start_date DATE NOT NULL,
 month_end_date DATE NOT NULL,
 CHECK (month_start_date < month_end_date));
 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

An SQL programmer would know to use ISO-8601 and use '2013-02-17' with the dashes as required by ANSI/ISO Standards.

Someone familiar with MS SQL Server as a product would know that you're better off without the dashes:

set language us_english
select cast('20130113' as datetime)
select cast('2013-01-13' as datetime)

set language british
select cast('20130113' as datetime)
select cast('2013-01-13' as datetime)

Changed language setting to us_english.

(1 row(s) affected)

(1 row(s) affected)
Changed language setting to British.

(1 row(s) affected)
Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.