date functions and variables in a view

• rwachalovsky

  Newbie

  Points: 1

  More actions

  November 20, 2008 at 5:56 pm

  #99369

  I need a calculation in a view where the following condition should be handled:

  If the current month is between July and December, then YEAR = current year +1, else YEAR = current year, so that the WHERE clause

  where table1.fiscal_year = YEAR works automatically and doesn't have to be updated manually.

  Anybody knows whether that can be done?

  Thanks for the help!

• winash

  SSChampion

  Points: 11446

  More actions

  November 21, 2008 at 3:29 am

  #901516

  I'm assuming you are looking for something like this:

  USE tempdb;

  GO

  IF OBJECT_ID('dbo.YearVw') IS NOT NULL

  DROP VIEW dbo.YearVw;

  GO

  IF OBJECT_ID('dbo.YearTab') IS NOT NULL

  DROP TABLE dbo.YearTab;

  GO

  CREATE TABLE dbo.YearTab(YearVal int, SomeOtherVal varchar(30));

  GO

  INSERT dbo.YearTab(YearVal,SomeOtherVal)

  SELECT 2008,'Year2008'

  UNION ALL

  SELECT 2009,'Year2009';

  GO

  CREATE VIEW dbo.YearVw

  AS

  SELECT

  YearVal

  ,SomeOtherVal

  FROM dbo.YearTab WHERE

  YearVal = CASE WHEN datepart(mm,getdate()) BETWEEN 6 AND 12 THEN datepart(yy,getdate())+1

  ELSE datepart(yy,getdate()) END

  GO

  SELECT * FROM dbo.YearVw;

  Also - please go through the article http://www.sqlservercentral.com/articles/Best+Practices/61537/ for tips on how to post code/data so that you can get the best response...