Date range - 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

Date range

Author

Topic

rudeone
Starting Member

2 Posts

Posted - 2006-06-18 : 21:28:42

Hi All,

I am very new to SQL and have a question, hopefully someone can answer.

I have a table of data, one of the fields is a date.

What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".

Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506
dates between 01/06/06 and 31/05/07 is fiscal year 0607

Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???

Any help is much appreciated.

Cheers

Rudi

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-18 : 21:33:24

It sounds like your system could use a fiscal year table giving the start and end dates and code. Then it's just a matter of finding the entry for the current date.

You could calculate it but it's more effort.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-18 : 21:53:01

[code]
declare @StartDt datetime
select @StartDt = '20000601'

declare @FY table(
FY int not null,
StartDate datetime not null,
EndDate datetime not null)

-- Load a table with the Midpoint, Start, and End for each time period
insert into @FY
select
FY = left(convert(varchar(30),dateadd(yy,a.number,@StartDt),112),6),
StartDate = dateadd(yy,a.number,@StartDt),
EndDate = dateadd(dd,-1,dateadd(yy,a.number+1,@StartDt))
from
-- Function from SQL Team Script Library Forum
dbo.F_TABLE_NUMBER_RANGE(0,11) a

select * from @FY
[/code]
Results:
[code]

FY StartDate EndDate
------ ----------------------- -----------------------
200006 2000-06-01 00:00:00.000 2001-05-31 00:00:00.000
200106 2001-06-01 00:00:00.000 2002-05-31 00:00:00.000
200206 2002-06-01 00:00:00.000 2003-05-31 00:00:00.000
200306 2003-06-01 00:00:00.000 2004-05-31 00:00:00.000
200406 2004-06-01 00:00:00.000 2005-05-31 00:00:00.000
200506 2005-06-01 00:00:00.000 2006-05-31 00:00:00.000
200606 2006-06-01 00:00:00.000 2007-05-31 00:00:00.000
200706 2007-06-01 00:00:00.000 2008-05-31 00:00:00.000
200806 2008-06-01 00:00:00.000 2009-05-31 00:00:00.000
200906 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000
201006 2010-06-01 00:00:00.000 2011-05-31 00:00:00.000
201106 2011-06-01 00:00:00.000 2012-05-31 00:00:00.000

(12 row(s) affected)
[/code]

CODO ERGO SUM

rudeone
Starting Member

2 Posts

Posted - 2006-06-18 : 22:16:28

I dont understand exactly whats happening here,

but am i right in assuming that i am setting up another table called F_TABLE_NUMBER_RANGE with those date ranges?

Is this something that can work in access?

quote:

Originally posted by Michael Valentine Jones


declare @StartDt  datetime
select @StartDt = '20000601'

declare @FY table(
FY	int not null,
StartDate datetime not null, 
EndDate datetime not null)

-- Load a table with the Midpoint, Start, and End for each time period
insert into @FY
select
	FY = left(convert(varchar(30),dateadd(yy,a.number,@StartDt),112),6),
	StartDate = dateadd(yy,a.number,@StartDt),
	EndDate = dateadd(dd,-1,dateadd(yy,a.number+1,@StartDt))
from
	-- Function from SQL Team Script Library Forum
	dbo.F_TABLE_NUMBER_RANGE(0,11) a

select * from @FY

Results:



FY      StartDate                EndDate
------  -----------------------  -----------------------
200006  2000-06-01 00:00:00.000  2001-05-31 00:00:00.000
200106  2001-06-01 00:00:00.000  2002-05-31 00:00:00.000
200206  2002-06-01 00:00:00.000  2003-05-31 00:00:00.000
200306  2003-06-01 00:00:00.000  2004-05-31 00:00:00.000
200406  2004-06-01 00:00:00.000  2005-05-31 00:00:00.000
200506  2005-06-01 00:00:00.000  2006-05-31 00:00:00.000
200606  2006-06-01 00:00:00.000  2007-05-31 00:00:00.000
200706  2007-06-01 00:00:00.000  2008-05-31 00:00:00.000
200806  2008-06-01 00:00:00.000  2009-05-31 00:00:00.000
200906  2009-06-01 00:00:00.000  2010-05-31 00:00:00.000
201006  2010-06-01 00:00:00.000  2011-05-31 00:00:00.000
201106  2011-06-01 00:00:00.000  2012-05-31 00:00:00.000

(12 row(s) affected)

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-18 : 22:57:55

quote:
I dont understand exactly whats happening here,

but am i right in assuming that i am setting up another table called F_TABLE_NUMBER_RANGE with those date ranges?

Is this something that can work in access?

F_TABLE_NUMBER_RANGE is a function table that generates the required number range. It can be found in here

This will works in MS SQL Server. If you are using MS Access, I don't think it support FUNCTION TABLE so you have to build your own table containing the number range that you required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-19 : 01:07:34

quote:
Originally posted by rudeone

Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???

Take a look at this

-- prepare test data
declare @test table (theDate datetime)

insert @test
select '2006-01-17' union all
select '2006-02-04' union all
select '2006-03-22' union all
select '2006-04-08' union all
select '2006-05-07' union all
select '2006-06-02' union all
select '2006-07-01' union all
select '2006-08-19' union all
select '2006-09-06' union all
select '2006-10-10' union all
select '2006-11-16' union all
select '2006-12-24'

-- do the work
select	theDate,
	'fiscal year' =	case
				when month(theDate) >= 6 then convert(varchar(2), theDate, 12) + convert(varchar(2), dateadd(yyyy, 1, theDate), 12)
				else convert(varchar(2), dateadd(yyyy, -1, theDate), 12) + convert(varchar(2), theDate, 12)
			end
from	@test

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-19 : 06:53:35

I would still have a fiscal year table

create table FiscalYear
(
FYCode varchar(10) ,
StartDate datetime ,
EndDate datetime
)

populate it with the values - maybe to the end of the century.

then your query would be

select fy.FYCode, ...
from FiscalYear fy
join mydate d
on d.dte between fy.StartDate and fy.EndDate
where convert(varchar(8),getdate(),112) between fy.StartDate and fy.EndDate

The where clause gets the current fiscal year and the join gets the data from the table in that year.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-19 : 08:47:34

quote:
Originally posted by rudeone

Is this something that can work in access?

Access queries has their own forum!

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-19 : 11:19:16

As Nigel says, create a separate table to store these periods. Do try not hard-code data into your SELECT statements. It will also be much more efficient to have your data in tables (since indexes can be used) and much easier to write multiple reports off of the same logic. And, of course, much easier to make adjustments to these periods as time goes on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources