Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Dynamic year help!

Dynamic year help!

Dynamic year help!

I was wandering if anyone can help me out. I want get rid of hard coded date and year instead make this range of date dynamic and by changing the year only.
I assume my (Fiscal Year)
@FY = '2018'
CASE BETWEEN (TDATE,{^2017-07-01},{^2018-06-30}) -- I want to make only Year Dynamic like {TDATE,{^(@YR-1)+(-07-01),{^(@YR)+(-06-30)}
YR = '2018'
CASE BETWEEN (TDATE,{^2016-07-01},{^2017-06-30}) -- {TDATE,{^(@YR-2)+(-07-01),{^(@YR-1)+(-06-30)}
YR = '2017'
CASE BETWEEN (TDATE,{^2015-07-01},{^2016-06-30}) -- {TDATE,{^(@YR-3)+(-07-01),{^(@YR-2)+(-06-30)}
YR = '2016'

I'm new to FoxPro programming and I am helping my company to fix the hardcoded years to dynamic fiscal year ending and beginning.

I mostly work on sql programming.

Really appreciate for quick help! :)


RE: Dynamic year help!

I can't tell if your CASE code above is supposed to be within a SQL Query command or just as 'regular' CASE/ENDCASE code.

Also, just give us the First and Last dates of your Fiscal Calendar (regardless of YEAR) so that we can better understand what you are trying to do.

BTW: to get a better understanding of VFP you might want to spend some time with the free, on-line VFP tutorial videos at: Link

Good Luck,

RE: Dynamic year help!

Thanks JRB,
First date would be 2017-07-01 and 2018-06-30 would be the last day of fiscal year.
BTW, thanks for the link. I'd definitely spend some time with this link.


RE: Dynamic year help!

With VFP, like with most other languages, there are many ways to approach an issue.

One way that comes to mind might be as follows:
EDIT: I keep thinking about how to change this. Here is the latest... (Sorry for the confusion)


* --- Enable 4 digit Year ---

* --- For any TDate, it is either <= Last Date or >= First Date ---
nTDateYr = YEAR(TDate)
   CASE MONTH(TDate) <= 6 AND DAY(TDate) <= 30
      * --- If Early Months of Year, Date is Already Within Fiscal Year ---
      nFiscYr = nTDateYr

   CASE MONTH(TDate) >= 7 AND DAY(TDate) >= 1
      * --- If Late Months of Year, Fiscal Year needs to be Pushed Out ---
      nFiscYr = nTDateYr + 1

* --- Change Numeric Fiscal Year To String ---
cFiscYr = STR(nFiscYr,4) 

NOTE - I have not tested the above code in ALL situations. And it 'feels' like the code could readily be simplified.

If you want the code integrated into a SQL Query statement, then it would be done somewhat differently.

Good Luck,

RE: Dynamic year help!

Thanks GRB, I'll let you know if the code works for me.


RE: Dynamic year help!

The question you have is T-SQL, so should rather be asked in forum183: Microsoft SQL Server: Programming

Your code seems to have some flaws, T-SQL variables are prefixed by @, not only when reading them, also when setting them, you set YR='2018', '2017', '2016', where that also should be @YR, rather SET @YTR=something. then I don't know if a string is better here than a number.

Given the outset, you want to pass in a year number from VFP into a T-SQL script that could work by VFPs TextMerge into a script you put together with TEXT...ENDTEXT and that would be the VFP part of the problem, but you could also fully solve that within SQL Server in a stored procedure with a parameter. The call within VFP then would be exec sp_yourstoredprocname(2017), for example. and the rest of the problem is to be solved in T-SQL.

Bye, Olaf.

RE: Dynamic year help!

CODE --> chunbabachun8

First date would be 2017-07-01 and 2018-06-30 

Since I wasn't sure which way your Fiscal Calendar went I assumed that the Fiscal Year 2017 went Forward from 7-1-17.
However if it goes back from 6-30-17 then you can readily reverse the logic.

Again, if you wanted the code to reside within a SQL Query statement, then you would need to modify it accordingly.


CODE -->

IIF(MONTH(TDate) <= 6 AND DAY(TDate) <= 30, YEAR(TDate), YEAR(TDate)+1) as FiscYear;
FROM DataTable;


Keep in mind that if your VFP code is doing a SQL Query into an 'external', non-native database (such as M$ SQL Server) you will need to use THAT Database's SQL Syntax, not VFP's.

Good Luck,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close