update a given date to end of the month

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)

update a given date to end of the month

Author

Topic

genic
Yak Posting Veteran

57 Posts

Posted - 2002-12-30 : 13:33:28

i am trying to parse through a date column and update the dates to the end of the month. if the field is 4/12/02 ...i need to update it to 4/30/02.

 update employee
set terminate_date = '2002-04-30' 
where terminate_date = '2002-04-12'

does anyone have a good example of this? i can get the start date of the month by

declare @curDate datetime
set @cureDate = getdate()
set @startDate = dateadd(d,(1 - day(@curDate)),@curDate)

but i am kinda lost how i can do the actual update statement and tell it which endate to update with.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-12-30 : 13:36:33

subtract one day from the next month.

Jonathan
{0}

genic
Yak Posting Veteran

57 Posts

Posted - 2002-12-30 : 13:49:50

quote:

subtract one day from the next month.

Jonathan
{0}

yes, i know i can do that, but the actual dynamic sql statement is whats getting me. i would like to do this in a SP with a supplied start/end date. it would look for all dates within the supplied range and update the dates with the end date.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-30 : 14:09:27

How about this:

CREATE FUNCTION usp_LastDayOfTheMonth
(@date datetime)
RETURNS int
AS
BEGIN
DECLARE @last int
SELECT @last = day(dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,@date),120)+'-01' as datetime)))
RETURN @last
END

Then call the function in the update statement.

Like this:

DECLARE @date datetime

SET @date = '04/13/2002'

UPDATE Employee
SET terminate_date = CONVERT(varchar(2), month(@date)) + '/' + CONVERT(varchar(2), dbo.dts (@date)) + '/' + CONVERT(varchar(4), year(@date))
WHERE terminate_date = @date

Edited by - tduggan on 12/30/2002 14:27:43

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 14:57:29

Also, look at this:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22339

My little function makes doing this stuff very easy.

if you create the function, then:

Update Table
Set oldDate = dbo.MDate(Year(oldDate),Month(oldDate)+1,1)-1

will convert the OldDate field to the month end.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-31 : 04:45:23

DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, terminate_date) + 1, 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources