Get the last day of the Month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get the last day of the Month

Author

Topic

andriancruz
Starting Member

38 Posts

Posted - 2004-02-05 : 03:40:37

Hi, All

I hope you doing fine. I have a question, How can I get the last day/date of the month in SQL 7.? There is a function/command to get this.?

For example. The month of February is only 29 days. How can I get the number 29. ?

I hope you can help me regarding this matter. Thank you in advance and more power to all of you

GOD BLESS TO ALL !!!

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-05 : 03:47:33

check this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21910

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

andriancruz
Starting Member

38 Posts

Posted - 2004-02-05 : 23:56:31

Hi, harshal_in

Thanks for the info. It's a big help for me.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-06 : 00:23:49

I did not follow the link above, so I hope this is not repeat code! Here is a function that I use all the time.

Just pass a date into it and it will give you back the last day of the month.

Have fun with it!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fun_GetLastDayOfMonth_asDateTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fun_GetLastDayOfMonth_asDateTime]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/**********************************************************************************************
Query Name : fun_GetLastDayOfMonth_asDateTime
Created By : AJM
Date : 11/21/2002
Description : This function returns the last day of the month for the date parameter.
***********************************************************************************************/

CREATE FUNCTION dbo.fun_GetLastDayOfMonth_asDateTime
(@DateIn DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @dtiEnd datetime
DECLARE @txtStart varchar(20)
DECLARE @txtEnd varchar(20)
DECLARE @Month varchar(10)
DECLARE @Year varchar(10)
DECLARE @RetVal as datetime

SET @Month = DATEPART(month, @DateIn)
SET @Year = DATEPART(year, @DateIn)

SET @txtStart = @month + '/1/' + @Year

SET @txtEnd = DATEADD(month, 1, @txtStart)
SET @txtEnd = DATEADD(day, -1, @txtEnd)

SET @dtiEnd = CAST(@txtEnd as datetime)

SET @RetVal = CONVERT(datetime, @dtiEnd, 101)

RETURN @RetVal

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-06 : 00:28:49

ajthepoolman,

If he can get that to work on SQL 7.0 I will be impressed!

DavidM

"SQL-3 is an abomination.."

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-06 : 14:55:09

Oooppss!!

Missed that whole SQL 7 part!

Ha!

<Aj ducks into a corner>

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources