How do i extract year, month and day from datetime field

[sultan123]

I want to increase employee salary according to information chart shown blew.

I have a hire.date field as datetime format for employee and I am increasing their salary field value by:

Date of
hire Salry
Year Range Raise
===== ====== =======
1991 >£75,000 10%
£35,000 - 75,000 8%
<35,000 6%

1992 >£45,000 9%
<=45,000 7%

1993 >£55,000 9%
£30,000 - 55,000 6%
<30,000 4%


1994 >£70,000 10%
<=70,000 5%


Any help would be hugely appreciated .

Many thanks in advance

 

[Mike Lewis]

Sultan32,

I take it you are asking how to find the year from a given datetime field? If so, just use the year() function:

IF year({employee.hire_date}) = 1991 etc

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[bdreed35]

There are 3 functions you can use,
Day({table.date})
Month({table.date})
Year({table.date})
to extract the requested information from a date field.

Please provide more information, such as Crystal version, database, etc., if you require more information.




~Brian

 

[synapsevampire]

The salary range is a problem as it only sometimes has currency symbols and less than and greater than signs. Is this really rther case, or did you incorrectly spec this??

What's even more important is the relationship between the tables involved, which you didn't share.

You can IF all of this out within CR, but if you're able to join year(doh) columns, and then create the criteria of the salary ranges within a View or SP you'll be well ahead of the game.

-k

 

[sultan123]

Thank you all for your help i have created a formula which perhaps uses too many if else statements but it works.. i am sure it would have been lot better using switch case???

anyway here is the formula:

IF year({Employee.Hire Date}) = 1991 then
(IF ( ({Employee.Salary} >= 35000 AND {Employee.Salary} <= 75000)) then
{Employee.Salary}*1.08
else
IF{Employee.Salary} > 75000 THEN
{Employee.Salary} * 1.1
ELSE
IF{Employee.Salary} < 35000 THEN
{Employee.Salary}*1.06
)
ELSE
IF year({Employee.Hire Date}) = 1992 then
(IF {Employee.Salary} > 45000 THEN
{Employee.Salary} * 1.09
ELSE
IF{Employee.Salary} <= 45000 THEN
{Employee.Salary}*1.07
)
ELSE
IF year({Employee.Hire Date}) = 1993 then
(IF ( ({Employee.Salary} >= 30000 AND {Employee.Salary} <= 55000)) then
{Employee.Salary}*1.06
else
IF{Employee.Salary} > 55000 THEN
{Employee.Salary} * 1.09
ELSE
IF{Employee.Salary} < 30000 THEN
{Employee.Salary}*1.04
)

ELSE
IF year({Employee.Hire Date}) = 1994 then
(IF {Employee.Salary} > 70000 THEN
{Employee.Salary} * 1.1
ELSE
IF{Employee.Salary} <= 70000 THEN
{Employee.Salary}*1.05
)

it works fine though looks a bit long!

any ideas on that?

 

[Mike Lewis]

Sultan123,

Instead of that deeply-nested IF/ELSE construct, I suggest you use a SELECT/CASE.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk