Current Month and previous 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)

Current Month and previous month

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-11 : 16:14:38

I am trying to edit the code below to get the current month up to the previous day.

(DATEADD(mm, DATEDIFF(m, 0, calendar_date), 0) = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

EX: I should get return data from April 1, 2012 thru April 10, 2012.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-11 : 16:34:45

where somedatecolumn >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) and somedatecolumn < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

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

Subscribe to my blog

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-11 : 16:40:53

@ tkizer

Thank you so much! That worked perfect. Is there a way to YTD with year begining 10/1. The catch to this not having to go backin update the code every year. I currently use this code below:

(calendar_date >= '10/01/2011') AND (calendar_date < DATEADD(d, 1, @EndDate))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-11 : 16:58:17

'10/01/' + CONVERT(char(4), YEAR(GETDATE()) - 1)

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 - 2012-04-12 : 03:17:36

[code]SELECT *
FROM dbo.Table1
WHERE Calendar_Date >= DATEADD(YEAR, DATEPART(YEAR, GETDATE()) - 1901, '19000110')
AND Calendar_Date < DATEADD(DAY, 1, @EndDate)[/code]

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

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-12 : 08:37:43

@ tkizer & SwePeso

Both YTD codes works, but they start prior to Oct 1st instead of startin on Oct 1st of the current year (fiscal year).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-04-12 : 10:56:54

This should give you the fiscal year start date for any given date.

SELECT
	a.DT,
	YearStart = dateadd(yy,datediff(yy,'19001001',dateadd(mm,-9,a.DT)),'19001001')
from
	( -- Test data
	select DT = getdate()					union all
	select DT = convert(datetime,'20110930 23:59:59.997')	union all
	select DT = convert(datetime,'20111001')		union all
	select DT = convert(datetime,'20120930 23:59:59.997')	union all
	select DT = convert(datetime,'20121001')
	) a

Results:

DT                       YearStart
-----------------------  -----------------------
2012-04-12 10:54:22.100  2011-10-01 00:00:00.000
2011-09-30 23:59:59.997  2010-10-01 00:00:00.000
2011-10-01 00:00:00.000  2011-10-01 00:00:00.000
2012-09-30 23:59:59.997  2011-10-01 00:00:00.000
2012-10-01 00:00:00.000  2012-10-01 00:00:00.000

(5 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-12 : 11:49:55

SELECT DATEADD(YEAR, DATEDIFF(MONTH, '19001001', theDate) / 12, '19001001') AS FiscalYearStart

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