Solved

sql function date compare

Posted on 2014-10-09
8
125 Views
Last Modified: 2014-10-09
I am dealing with 4 date fields in a function    

@AsOfDate (passed to function)
@PaidDate (variable in function)
table.AppliedDate  
table.PostDate

I want to set the value of the @PaidDate to the table.AppliedDate but if the table.AppliedDate is Null then I want to set the @PaidDate to table.PostDate

then I only want to select those records where the @PaidDate is <= to @AsOfDate.

I don't know how to do this.
0
Comment
Question by:ryann
[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
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40370749
Show us the function and the table schema(s) involved here.
Keep in mind that a prefix of @ indicates a variable.
0
 

Author Comment

by:ryann
ID: 40370771
Hope this helps.

I have not written the function yet. But here is the shell. I don't know how to set the @PaaidDate to the right date or where to put it in the sql

Declare @AsOfDate         (this will be passed to function)
                @PaidDate        (this will be the table.applied date or the table.PostDate

Select *
   From Table
       where
              @PaidDate <= @AsOfDate
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40370780
>this will be the table.applied date or the table.PostDate
Spell out the 'OR' logic involved here.

So far, this will go something like...

SELECT *
FROM Table
WHERE
   ( <or logic goes here that evaluates to TRUE> AND @PaidDate <= @AsOfDate)
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:ryann
ID: 40370810
Select *
From Table
      If isnull table.appliedDate then
            @PaidDate = table.PostedDate
     else
            @PaidDate=table.AppliedDate

where @PaidDate <= @AsOfDate
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40370823
>where @PaidDate <= @AsOfDate
Since these are both variables, that means the entire SELECT query will return all or nothing based on this.  
(Question #1)  Correct?  And what happends if the above is false?

>else @PaidDate=table.AppliedDate
(Question #2)  SQL doesn't allow assignment of values to a variable, and a SELECT query that returns values, in the same SELECT clause, so what are you trying to pull off here?
0
 

Author Comment

by:ryann
ID: 40370844
Yes you are correct. If it is false then I don't want the record.
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40370883
Not sure, why you need a function..

DECLARE @AsOfDate DATE;
DECLARE @Table TABLE
    (
      AppliedDate DATE ,
      PostDate DATE
    );

SELECT  T.*
FROM    @Table T
WHERE   COALESCE(T.AppliedDate, T.PostDate) < @AsOfDate;

Open in new window

0
 

Author Closing Comment

by:ryann
ID: 40370919
New to SQL so had to look up Coalesce but it worked. Thanks!
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 1 38
how to extract last 6 digits from a varchar, sql 4 24
SQL Syntax 6 43
SQL Query Returns Records in SSMS but not Classic ASP 5 26
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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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