Solved

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

Posted on 2008-06-15
6
896 Views
Last Modified: 2008-11-02
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
Comment
Question by:digital_slavery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 250 total points
ID: 21788366
use the YEAR function
select right('0' + right(cast(year(yourdatefield) as varchar(4)),2),2) as yy,
....etc
from yourtable
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21788444
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 21788640
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 63
Database Availability Group Distribution 9 49
exchange 2010 Dag failed 3 65
Adjective-nouns, noun-adjectives correlation dictionary? 2 37
Read about achieving the basic levels of HRIS security in the workplace.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question