DATE QUESTION - 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 2005 Forums

Transact-SQL (2005)

DATE QUESTION

Author

Topic

v_kash
Starting Member

45 Posts

Posted - 2010-08-23 : 16:31:28

Hello: I have a quick question. i want to run something each monday for the following MTD, WTD, YTD.

we currently just enter in the date values in the stored proc and run it accordinly. we want to now automate it.

we ran it today and the dates for each filter are

MTD - 8/1/10 - 8/20/10
WTD - 8/14/10 - 8/20/10
YTD - 5/1/10 - 8/20/10 (5/1/10 is the begging of our fiscal year)

for the WTD, we pull for the last prior Saturday - Friday (saturday was 8/14, and Friday was 8/20).

how do we do this in sql?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 17:08:07

[code]DECLARE @Today DATETIME

SET @Today = '20100820'

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Today), 0) AS [MTD],
DATEADD(DAY, DATEDIFF(DAY, 5, @Today) / 7 * 7, 5) AS [WTD],
DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(DAY, -4, @Today)), 4) AS [YTD],
DATEADD(DAY, DATEDIFF(DAY, 0, @Today), 0) AS [To][/code]

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-23 : 17:23:29

You may need to tweek the start of start of month and start of fiscal year calculation. You didn't say if the start of month is based on the start of the week or the end of the week, so I used the start of the week.


declare @current_datetime		datetime
declare @last_friday			datetime
declare @saturday_before_last_friday	datetime
declare @start_of_month			datetime
declare @start_of_fiscal_year		datetime

select	@current_datetime = getdate()

select	@last_friday 			= dateadd(dd,(datediff(dd,-53686,@current_datetime)/7)*7,-53686)
select	@saturday_before_last_friday 	= dateadd(dd,(datediff(dd,-53685,@last_friday)/7)*7,-53685)
select	@start_of_month			= dateadd(mm,datediff(mm,0,@saturday_before_last_friday),0)
select	@start_of_fiscal_year		= dateadd(mm,4,dateadd(yy,datediff(yy,0,dateadd(mm,-4,@saturday_before_last_friday)),0))

set nocount on
select  [@current_datetime]		= @current_datetime
select	[@last_friday] 			= @last_friday
select	[@saturday_before_last_friday] 	= @saturday_before_last_friday
select	[@start_of_month]		= @start_of_month
select	[@start_of_fiscal_year]		= @start_of_fiscal_year

Results:


@current_datetime                                      
------------------------------------------------------ 
2010-08-23 17:32:43.390

@last_friday                                           
------------------------------------------------------ 
2010-08-20 00:00:00.000

@saturday_before_last_friday                           
------------------------------------------------------ 
2010-08-14 00:00:00.000

@start_of_month                                        
------------------------------------------------------ 
2010-08-01 00:00:00.000

@start_of_fiscal_year                                  
------------------------------------------------------ 
2010-05-01 00:00:00.000

CODO ERGO SUM

v_kash
Starting Member

45 Posts

Posted - 2010-08-24 : 09:38:06

Thanks guys. I consider myself ok at sql, but terrible when doing stuff with dates! Michael - I didn't understand your question about start of month is based on start of week or end of week? Start of month is always the first day of the month. Not sure if that answered your ? Thanks once again

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-24 : 12:18:39

quote:
Originally posted by v_kash

Thanks guys. I consider myself ok at sql, but terrible when doing stuff with dates! Michael - I didn't understand your question about start of month is based on start of week or end of week? Start of month is always the first day of the month. Not sure if that answered your ? Thanks once again

The start of which month? For example, the beginning of week might be Saturday, August 28, 2010, while the end of the week would be Friday, September 3, 2010, while the current day is Tuesday, September 7, 2010. So is the month the same as the start of week date (August), end of week date (September), or the current date (September)? The same questions apply to the Fiscal year.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources