Find the 20th just passed - 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

General SQL Server Forums

New to SQL Server Programming

Find the 20th just passed

Author

Topic

Movember
Starting Member

12 Posts

Posted - 2010-04-28 : 19:09:32

Hi,

From the current date i need to find the last 20th of the month that just passed. Is this possible?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 19:20:42

Yes.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:21:09

DECLARE @d datetime

SET @d = DATEADD(month, -1, DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0))

SET @d = DATEADD(day, -DAY(@d)+20, @d)

PRINT @d

You can combine them into one statement, however it gets confusing. The idea is to subtract off one month from today, subtract off the day portion and then add 20.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:21:29

quote:
Originally posted by Peso

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 19:21:35

[code]SELECT DATEADD(MONTH, DATEDIFF(MONTH, 31, GETDATE()), 19)[/code]

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:22:43

Or use Peter's, lol!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Movember
Starting Member

12 Posts

Posted - 2010-04-28 : 19:38:11

Both of these statements pick up March 20th not April 20th which is what I am trying to get.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:39:39

Your post said for the month that just passed. We are still in April, so it hasn't passed yet.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:47:52

Here's the correction on mine with your new requirement:

DECLARE @d datetime

SET @d = DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)

SET @d = DATEADD(day, -DAY(@d)+20, @d)

PRINT @d

I don't understand Peter's, otherwise I'd post the correction for his too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-28 : 19:59:15

You didn't specify the edge condition, for which month to return the 20th when today is the 20th, so I wrote two cases.

DECLARE	@Sample TABLE
	(
		theDate DATETIME
	)

INSERT	@Sample
	(
		theDate
	)
SELECT	'20100419' UNION ALL
SELECT	'20100420' UNION ALL
SELECT	'20100421'

SELECT	theDate,
	DATEADD(DAY, -DAY(theDate) + 20, theDate) AS tKizer,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -19, theDate)), 19) AS Peso1,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -20, theDate)), 19) AS Peso2
FROM	@Sample

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

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-04-28 : 20:45:38

Peso's example is very simple - really...

If we take the 0 date in SQL Server is 1900-01-01, then the following will always give us the first of the month specified in theDate:

DATEADD(MONTH, DATEDIFF(MONTH, 0, theDate), 0)

Which says, get the difference in months between theDate and our 0 (1900-01-01) date. Then, add that many months to our 0 date to get to the first of the month in theDate.

Since the 0 date is always the first, then the 19th date will always be the 20th of the month.

I prefer the following to get the 20th of this month.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 19, theDate), 19)

To get the 20th of the previous month, we can do either of these:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 19, theDate) - 1, 19)
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -12, theDate), -12) -- -1 date would be 1899-12-31, so -12 is 1899-12-20

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources