save a date (last day of 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 2008 Forums

Transact-SQL (2008)

save a date (last day of month)

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 12:16:05

hi everyone

that i need is pretty simple, but i neeed your help

when i insert a record on a table i have got a column called "date", and i need to inset in this column the date of the last day of the last month

example today is (mm/dd/yyyy ) 04/17/2012, so i have to insert in this column this date 03/31/2012 (the last day of the las month before the current date"

any script any help that could help me

many many thanks in advanced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 12:20:42

[code]DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,'19000101',GETDATE()),'19000101'))[/code]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-17 : 12:30:53

Sunita the xml goddess comes through yet again. glad you are now employed

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-17 : 12:53:11

awesome
thanks a lot sunitabeck

kind regards

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-04-17 : 16:26:51

A slightly shorter solution:

select LM = dateadd(mm,datediff(mm,-1,'04/17/2012')-1,-1)

Results:

LM
-----------------------
2012-03-31 00:00:00.000

(1 row(s) affected

It could also be coded this way:

select LM = dateadd(mm,datediff(mm,'18991231','04/17/2012')-1,'18991231')

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-18 : 00:17:42

quote:
Originally posted by Michael Valentine Jones

select LM = dateadd(mm,datediff(mm,'18991231','04/17/2012')-1,'18991231')

Michael, you can drop the -1 too.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', '04/17/2012'), '18991231') AS LM

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-18 : 04:23:36

If Time doesn't matter then this must be very short

select getdate()-day(getdate())

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-04-18 : 15:21:24

quote:
Originally posted by madhivanan

If Time doesn't matter then this must be very short

select getdate()-day(getdate())

Madhivanan

Failing to plan is Planning to fail

select
	a.DT,
	LM1 = dateadd(mm,datediff(mm,0,a.DT),-1) ,
	LM2 = convert(date,a.DT-day(a.DT))
from
	(  -- Test date
	select DT = getdate()	union all
	select '04/17/2012'
	) a

Results:

DT                      LM1                     LM2
----------------------- ----------------------- ----------
2012-04-18 14:16:03.397 2012-03-31 00:00:00.000 2012-03-31
2012-04-17 00:00:00.000 2012-03-31 00:00:00.000 2012-03-31

(2 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-18 : 18:56:39

Sorry Michael, that was my typo.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '18991231', '04/17/2012'), '18991231') AS LM

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources