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
Solved

calculating dates

Posted on 2014-04-02
9
241 Views
Last Modified: 2014-04-03
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
Comment
Question by:metropia
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39972939
won't that logic always just return today's date?

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

Author Comment

by:metropia
ID: 39972967
sorry my bad.

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 40

Accepted Solution

by:
Sharath earned 350 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

Open in new window

0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

Author Comment

by:metropia
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)

Open in new window

0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 50 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

by:awking00
awking00 earned 50 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

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

Assisted Solution

by:PortletPaul
PortletPaul earned 50 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

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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