• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 906
  • Last Modified:

SQL how to format result set IF variable date is >= or <=

I am writing a report that needs to display a column based off a date column. In my example I need the query to take the date that is already in the column and display an identifier based off which year it is. My query uses 2 dates as parameters to get a result set.

for example:
Today is June 15th 2008, so when the query is ran today and the startDate param is Sept 1 2007  and the end date is June 14th 2008 the query would need to analyze the results set to format the column to display either 07 for any items that have 2007 and 2008 for any thing that has 2008 ect ect. I would acutally love it if I could automate this so it does not matter what year the query could just keep populating the column as each year passes. So next year the column would automatically out put 09 for the FY column.

Also the FY  is from Oct 1st to Sept 31 which the query needs to use when determining how to format the results.
0
digital_slavery
Asked:
digital_slavery
  • 2
2 Solutions
 
frankyteeCommented:
use the YEAR function
select right('0' + right(cast(year(yourdatefield) as varchar(4)),2),2) as yy,
....etc
from yourtable
0
 
frankyteeCommented:
didn't read your question properly before, try:

select
  case
    when month(yourdatefield) between 10 and 12 then      --next year
        right('0' + right(cast((year(yourdatefield) + 1) as varchar(4)),2),2)
    else  --this year
       right('0' + right(cast((year(yourdatefield)) as varchar(4)),2),2)
  end as FY
.... etc
from yourtable
0
 
Mark WillsTopic AdvisorCommented:
Interesting, can also be readily handled in most report writers as well...

I have simply used getdate()  as an example - you should replace with the appropriate columns etc etc...

select right(year(getdate()),2) as current_YY, right(year(dateadd(mm,3,getdate())),2) as current_fy

do you also want the WHERE clause to calculate start of financial year up to "yesterday" ?  This time we will convert to datetime so it can be used directly in a where

select convert(datetime,'01 Oct '+convert(varchar,year(dateadd(mm,3,getdate()-1)))) as Start_of_FY,
       convert(datetime,'30 Sep '+convert(varchar,year(dateadd(mm,3,getdate()-1)))) as End_of_FY,
       convert(datetime,convert(varchar, getdate() -1,106)+' 23:59:59.9') as yesterday
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now