Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# calculating dates

Posted on 2014-04-02
Medium Priority
250 Views
I need to calculate a filed named: ContractOpenBeginDate

The concetp of the field is as follows:

ContractOpenBeginDate =  to Today's date if in the middle of contract (start & end dates) OR if contract has not started yet OR NULL if Contract is closed (contract end date is in the past)

The fields I will use are: Today (GETDATE()), ContractStartDate, ContractEndDate

I know this is a CASE statement, but I need help in writing the code if possible because I have not used these type of combinations before.

Any help would be great.

Thank you very much fro your help.
0
Question by:metropia
[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
• 3
• 2
• 2
• +2

LVL 40

Expert Comment

ID: 39972939

when would the ContractOpenBeginDate  not be today's date?
0

Author Comment

ID: 39972967

use today if today falls in the middle of contract start and contract end date

use contract start date if that date is greater than today (future)

use null if contract is closed, or its end date is in the past.
0

LVL 41

Accepted Solution

Sharath earned 1400 total points
ID: 39972988
``````CASE WHEN ContractStartDate <= CONVERT(DATE,GETDATE()) AND ContractEndDate >= CONVERT(DATE,GETDATE()) THEN CONVERT(DATE,GETDATE())
WHEN ContractStartDate > CONVERT(DATE,GETDATE()) THEN ContractStartDate
WHEN ContractEndDate < CONVERT(DATE,GETDATE()) THEN NULL END
``````
0

Author Comment

ID: 39973002
I came up with this statement but not sure if it is syntactically, logiaclly correct to the concept:

``````,    [ContractOpenStartDate] =
(CASE
WHEN @Today BETWEEN [vsol].[ContractStartDate] AND [vsol].[ContractEndDate]
THEN @Today
ELSE
CASE
WHEN [vsol].[ContractStartDate] >  @Today THEN [vsol].[ContractStartDate]
WHEN [vsol].[ContractEndDate] < @Today THEN NULL
END
END)
``````
0

LVL 40

Assisted Solution

Kyle Abrahams earned 200 total points
ID: 39973055
your case statement looks fine as long as you set @today to be getdate. Sharath also posted a solution which works.

Note you don't need the else case . . . you can just continue the "whens"

(CASE
WHEN @Today BETWEEN [vsol].[ContractStartDate] AND [vsol].[ContractEndDate]
THEN @Today
WHEN [vsol].[ContractStartDate] >  @Today THEN [vsol].[ContractStartDate]
WHEN [vsol].[ContractEndDate] < @Today THEN NULL
END
)
0

LVL 32

Assisted Solution

awking00 earned 200 total points
ID: 39973350
Be careful using between with dates. If ContractEndDate doesn't include a time value then, for example, 12-31-2014  would be considered to be 12-31-2014 and, if getdate() is 12-31-2014 10:30:44, it will not be considered to be between and return null. It is best to use where getdate() >= ContractBegnDate and get_date() < dateadd(day,1,ContractEndDate)
0

LVL 32

Expert Comment

ID: 39973353
I meant to say "would be considered to be 12-31-2014 00:00:00" in the above comment.
0

LVL 49

Assisted Solution

PortletPaul earned 200 total points
ID: 39973867
>>"Be careful using between with dates."
absolutely!

You will notice that in Sharath's suggestion he is converting getdate() to a date so that time within a day may be ignored. By using that approach you could use between.

Alternatively as awking00 has proposed you may continue to use an unconverted getdate() but then do not use between.

see: "Beware of Between"

So, if you adopt the conversion of getdate() to date for @Today then between is safe to use.

set @Today = convert(date, getdate() )

or

set @Today = dateadd(day, datediff(day,0, getDate() ), 0)

then the case expression
0

Author Closing Comment

ID: 39975515
Thank you very much for the solution and all the good recommendations :)
0

## Featured Post

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month11 days, 6 hours left to enroll