Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with Date format convert Month 1 to 01.

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts
Posted - 2010-02-19 : 10:58:06
How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace
date range like Month 1 -> 01
2 -> 02
...
9 -> 09

Above is the input search parameter and the search failed because user input 1/6/2010.
Any helps would greatly appreciated.


DECLARE @SearchVal VARCHAR(50)
SET @SearchVal = '1/8/2010' --9/4/2010 should return 09/04/2010
SELECT @SearchVal

Expect result:
---------
01/08/2010

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 11:00:05
You should be storing your dates in a DATETIME datatype - not a varchar.

Then you could format them how you liked when you displayed them, as well as being able to ORDER BY chronologically, DIFF them, ADD days / months / year etc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 11:01:00
first question
why are you storing date values in varchar field?
If its format that worries you, you dont need to be concerned about format while storing dates. you can always display them in format you want at front end. So if possible, change data types to datetime and store date values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 11:01:18


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-02-19 : 11:06:32
So, let me see if I understand your situation. You have a column in your database that has date values, but those values are stored as a VARCAHR. Additionally, those VARCAHR "dates" are in the format DD/MM/YYYY. Where the Day and Month are both exactly two characters long (padded with a zero) and the Year is always four characters long. Is that correct?

If so, try using the CONVERT fucntion to convert the paramter to a date then back to a string:
DECLARE @SearchVal VARCHAR(50)
SET @SearchVal = '1/8/2010' 

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, @SearchVal, 103), 103)
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts
Posted - 2010-02-19 : 11:16:44

The colum datatype defined as DATETIME but the search para define as varchar(50). This search parameter search for multiple columns just like Google search. I try to do CONVERT(CHAR(10), @Searchpara, 101) as input para but when I test the SP, user might input 1/4/2010 then
the search failed but when I input 01/04/2010 then it works fine.

I am getting error when I change to different date.

DECLARE @SearchVal VARCHAR(50)
SET @SearchVal = '1/15/2010'

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, @SearchVal, 103), 103)

--errors.

----------
Msg 242, Level 16, State 3, Line 4
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-02-19 : 11:21:28
If your column is s datetime, then all you need to do is convert the parameter to a DATETIME:
SELECT <columns>
FROM <table>
WHERE DateColumn = CONVERT(DATETIME, @SearchVal, 103)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 11:33:16
" The colum datatype defined as DATETIME but the search para define as varchar(50)."

OK, common scenario.

Some possible solutions:

1) Have the application re-format the date as 'ccyymmdd' before it sends it, or store the date into a native DATETIME variable and send that as a parameter (then it will never be in [ambiguous] string format).

2) Convert it to DATETIME datatype as soon as you get it in SQL.

DECLARE @MyDateVar DATETIME

SET @MyDateVar = CONVERT(DATETIME, @SearchVal, 103)

will convert it if it is d/m/y style, or

SET @MyDateVar = CONVERT(DATETIME, @SearchVal, 101)

if it is m/d/y style.

3) Provide a hint for the date format, and then SQL will use that during its implicit date conversion:

SET DATEFORMAT mdy

SET @MyDateVar = CONVERT(DATETIME, @SearchVal)

However, how do you control that users will enter the date in your preferred m/d/y format? If it is an internet application, used by people around the world, then you need something to validate the date as it is entered - and if your app can do that then it can probably also reformat it as 'ccyymmdd' and then you won't have to worry about date conversion in SQL at all
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts
Posted - 2010-02-19 : 11:38:25
This won't work. Here is the actual code. And this
piece of CONVERT(DATETIME, a.LastUpDate, 103) is not work.

sp_help Mytable

Column_name Type Nullable
--------------- ----------- --------
LastUpDate datetime yes


ALTER PROC [v2].[spMain_PipelineSearch2]
(
@SearchVal VARCHAR (50) = NULL
)
AS
SET NOCOUNT ON
DECLARE @startDate DATETIME
,@dtWarningDueDate DATETIME
,@dtOverDueDate DATETIME
SET @startdate = DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

SELECT @dtWarningDueDate = dtDiligenceNeeded,
@dtOverDueDate = dtExpireddiligenceneeded
FROM tblDiligenceNeededDate
WHERE (dtDate = @startdate)

--SELECT @dtWarningDueDate AS 'dtWarningDueDate', @dtOverDueDate AS 'dtOverDueDate'

SET @SearchVal = LTRIM(RTRIM(@SearchVal)) + '%'

SELECT a.LoanNum AS [KCC Loan],
a.PoolNum AS [Pool],
CAST(CASE
WHEN parentLoan.LoanNum IS NULL THEN (0)
ELSE 1
END AS BIT) AS [Linked],
a.LoanStatus AS [Status],
a.AssetManager AS [Asset Manager],
a.AEExt AS [AM Extension],
NULLIF(a.LastUpDate, '') AS [Last Update],
a.BorrLast + COALESCE(', ' + nullIf(a.BorrFirst, ''),'') AS [Borrower Name],
a.BorrFirst AS [Borrower First Name],
a.BorrLast AS [Borrower Last Name],
a.BorrSSN AS [Borrower SSN],
a.PropertyCity AS [City],
a.PropertyState AS [State],
a.PropertyZip AS [Zip],
a.PropertyCounty AS [PropertyCounty],
m1.MainCategoryDesc AS [Exit Strategy],
NullIf(a.AssetExitDate,'') AS [Exp Asset Exit Date],
cast(isnull(a.BPOVal ,0) as decimal(20,2)) AS [Collateral Value],
NullIf(a.BPODate,'') AS [Collateral Value Date],
cast(isnull(ExitInfo.BidAmount,0) as decimal(20,2)) AS [Bid Amount],
cast((ExitInfo.GrossProceeds + a.AddlRevenue - ExitInfo.AcqCost - a.CarryCosts - ExitInfo.AddlAssetCost) as decimal(20,2)) AS [KCC Gain],
cast(isnull(ExitInfo.GrossProceeds,0) as decimal(20,2)) AS [Gross Expected Wire],
cast(isnull(ExitInfo.AcqCost,0) as decimal(20,2)) AS [Acquisition Cost],
NullIf(a.AcquisitionDate, '') AS [Acquisition Date],
cast(isnull(a.CurrentUPB,0) as decimal(20,2)) AS [Current UPB],
a.LienPosition AS [Lien],
a.AssetCoordinator AS [Asset Coordinator],
a.PortfolioMgr AS [Portfolio Mgr],
a.PMExt AS [PM Extension],
pd.FirstName + ' ' + pd.LastName AS [Portfolio Dir],
pd.Extension AS [Portfolio Dir Ext],
a.SellerLoanID AS [Seller Loan ID],
a.ServicerID AS [Servicer ID],
a.PropertyAddr1 AS [Address],
a.NoteType AS [1st TD Note Type],
a.CurrIntRate AS [1st TD Interest Rate],
cast(isnull(a.CurrBalance,0) as decimal(20,2)) AS [1st TD Balance],
cast(isnull(a.PandI,0) as decimal(20,2)) AS [1st TD Payment],
NullIf(a.FirstPayDate, '') AS [1st TD First Payment Date],
NullIf(Loan.LastPmtRecvdDate, '') AS [1st TD Last Payment Date],
NullIf(a.NextPayDate, '') AS [1st TD Next Payment Date],
a.OtherLienLoanNum AS [Concurrent Lien],
a.OtherLienIntRate AS [2nd TD Interest Rate],
cast(isnull(a.OtherLienBal,0) as decimal(20,2)) AS [2nd TD Balance],
cast(isnull(a.OtherLienPandI,0) as decimal(20,2)) AS [2nd TD Payment],
NullIf(a.OtherLastPay, '') AS [2nd TD Last Payment Date],
NullIf(a.OtherNextPay, '') AS [2nd TD Next Payment Date],
Audits.PurchFlag AS [Unclear to Purchase],
ExitInfo.InBKFlag AS [InBK],
a.BorrowerRepByAtty AS [Rep by Atty],
B.EndDate AS [NextPaymentDate], -- Kept_broke_date,
cast(isnull(B.PmtAmount,0) as decimal(20,2)) AS [NextPaymentAmount], --repay_monthly_pay,
case when IsNull(RepayStatus, 3) in (3,6) then 0 else 1 end AS [RepaymentLoan],
isNull(C.StatusName,'') AS [RepaymentBrokenOrKept],
case when B.EndDate < getDate() then 1 else 0 end AS [IsPlanCompleted],
B.EndDate AS [StopCodeEndDate],
TradeRequirements.ServTransfer AS [Service Transfer Date],
CASE
when (a.LoanStatus = 'Portfolio' and DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) <= @dtOverDueDate ) then 1
else 0
END AS [LastUpdateFlag],
CASE
WHEN (a.LoanStatus = 'Portfolio' and DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) <= @dtWarningDueDate and
DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) > @dtOverDueDate ) then 1
ELSE 0
END AS [WarningUpdateFlag],
CASE
when (a.LoanStatus = 'Portfolio' and a.AssetExitDate < getdate()) then 1
else 0
END AS [ExpiredClosingDateFlag],
CASE
when (a.LoanStatus = 'Portfolio' and datediff (d, getdate(), a.AssetExitDate) > 0 and datediff (d, getdate(), a.AssetExitDate ) < 8 )then 1
else 0
END AS [UpcomingCloseFlag]
FROM dbo.KondaurData AS a
LEFT JOIN dbo.Audits on a.LoanNum = Audits.LoanNum
LEFT JOIN dbo.ExitInfo ON a.LoanNum = ExitInfo.LoanNum
LEFT JOIN dbo.exitstrategycategorymain m1 on exitinfo.ExitStrategyMainCategoryID = m1.MaincategoryID
LEFT JOIN dbo.Logins l on a.AssetManager = l.UserName
LEFT JOIN dbo.tblKon_PortfolioDirectors AS pd
ON l.PortfolioDirectorID = pd.PortfolioDirectorID
LEFT JOIN dbo.KondaurData parentLoan on a.LoanNum = parentLoan.OtherLienLoanNum -- is loan an other lien?
LEFT JOIN service.dbo.Loan on a.LoanNum = right(Loan.LoanID,6)
LEFT JOIN service.dbo.RepayPlan B on a.LoanNum = right(B.LoanID,6) and B.RepayStatus in (1,3,4,5,6)
LEFT JOIN service.dbo.RepaymentStatuses C on B.RepayStatus = C.Code
LEFT JOIN dbo.TradeRequirements on a.LoanNum = TradeRequirements.LoanNum
WHERE (a.LoanNum LIKE @SearchVal OR a.PoolNum LIKE @SearchVal OR a.PropertyAddr1 LIKE @SearchVal OR a.PropertyCity LIKE @SearchVal OR
a.PropertyState LIKE @SearchVal OR a.PropertyZip LIKE @SearchVal OR CONVERT(DATETIME, a.LastUpDate, 103) LIKE @SearchVal OR
CONVERT(CHAR(10), a.AssetExitDate, 103) LIKE @SearchVal OR a.AssetManager LIKE @SearchVal OR a.PortfolioMgr LIKE @SearchVal OR
a.SellerLoanID LIKE @SearchVal OR a.ServicerID LIKE @SearchVal OR pd.FirstName LIKE @SearchVal OR pd.LastName LIKE @SearchVal OR
a.BorrLast LIKE @SearchVal OR a.BorrFirst LIKE @SearchVal)
ORDER BY a.LoanNum ASC;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 11:43:16
you dont need to convert field to datetime as you've it defined already as datetime type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 12:02:41
This perhaps?

ALTER PROC [v2].[spMain_PipelineSearch2]
(
@SearchVal VARCHAR (50) = NULL
)
AS
...
DECLARE @SearchDate DATETIME
...
SET DATEFORMAT mdy
IF IsDate(@SearchDate) = 1 SET @SearchDate = CONVERT(DATETIME, @SearchDate)
...
SET @SearchVal = LTRIM(RTRIM(@SearchVal)) + '%' -- NOTE: Do this AFTER setting @SearchDate
...
WHERE (a.LoanNum LIKE @SearchVal OR a.PoolNum LIKE @SearchVal OR a.PropertyAddr1 LIKE @SearchVal 
    OR a.PropertyCity LIKE @SearchVal OR a.PropertyState LIKE @SearchVal OR a.PropertyZip LIKE @SearchVal 
    OR a.LastUpDate = @SearchDate OR a.AssetExitDate = @SearchDate OR a.AssetManager LIKE @SearchVal 
    OR a.PortfolioMgr LIKE @SearchVal OR a.SellerLoanID LIKE @SearchVal OR a.ServicerID LIKE @SearchVal 
    OR pd.FirstName LIKE @SearchVal OR pd.LastName LIKE @SearchVal OR a.BorrLast LIKE @SearchVal OR a.BorrFirst LIKE @SearchVal)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 12:04:45
Its going to run like a dog though hopefully you don't have more than a few thousand records in [KondaurData] and its associated tables.
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts
Posted - 2010-02-19 : 13:05:44
[code]DECLARE @SearchVal VARCHAR(50)
SET @SearchVal = '1/8/2010' --9/4/2010 should return 09/04/2010
SELECT @SearchVal

Expect result:
---------
01/08/2010
[/code]


Try this

DECLARE @SearchVal VARCHAR(50)
SET @SearchVal = '1/8/2010' --9/4/2010 should return 09/04/2010
SELECT @SearchVal OriginalValue, CONVERT(VARCHAR(10), CONVERT(DateTime,@SearchVal), 101) AS [MM/DD/YYYY FORMAT]
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 13:13:13
Yeah, but it turns out that is not the issue if you read the thread ...
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts
Posted - 2010-02-19 : 13:22:25
Thank you All for helping me.
Have a good Weekend.

L

quote:
Originally posted by Kristen

This perhaps?

ALTER PROC [v2].[spMain_PipelineSearch2]
(
@SearchVal VARCHAR (50) = NULL
)
AS
...
DECLARE @SearchDate DATETIME
...
SET DATEFORMAT mdy
IF IsDate(@SearchDate) = 1 SET @SearchDate = CONVERT(DATETIME, @SearchDate)
...
SET @SearchVal = LTRIM(RTRIM(@SearchVal)) + '%' -- NOTE: Do this AFTER setting @SearchDate
...
WHERE (a.LoanNum LIKE @SearchVal OR a.PoolNum LIKE @SearchVal OR a.PropertyAddr1 LIKE @SearchVal 
    OR a.PropertyCity LIKE @SearchVal OR a.PropertyState LIKE @SearchVal OR a.PropertyZip LIKE @SearchVal 
    OR a.LastUpDate = @SearchDate OR a.AssetExitDate = @SearchDate OR a.AssetManager LIKE @SearchVal 
    OR a.PortfolioMgr LIKE @SearchVal OR a.SellerLoanID LIKE @SearchVal OR a.ServicerID LIKE @SearchVal 
    OR pd.FirstName LIKE @SearchVal OR pd.LastName LIKE @SearchVal OR a.BorrLast LIKE @SearchVal OR a.BorrFirst LIKE @SearchVal)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 13:24:25
quote:
Originally posted by NguyenL71

Thank you All for helping me.
Have a good Weekend.

L


Does that mean your issue got sorted?
Can we see final working code then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 13:32:56
"Does that mean your issue got sorted"

Yeah, my code was perfect and the pigs are fed, and ready to fly
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 14:04:11
quote:
Originally posted by Kristen

"Does that mean your issue got sorted"

Yeah, my code was perfect and the pigs are fed, and ready to fly



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts
Posted - 2010-02-19 : 17:38:20
Yes. It's working. Just to clarify, I am inheritage the codes to edit with the new requirements.

ALTER PROC [v2].[spMain_PipelineSearch2]
(
@SearchVal VARCHAR (50) = NULL
)
AS
SET NOCOUNT ON
SET DATEFORMAT mdy
DECLARE @datesearch DATETIME

IF (ISDATE(@SearchVal) = 1)
BEGIN
SET @datesearch = CONVERT(DATETIME, @SearchVal)
--SELECT @datesearch
END

DECLARE @startDate DATETIME
,@dtWarningDueDate DATETIME
,@dtOverDueDate DATETIME
SET @startdate = DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

SELECT @dtWarningDueDate = dtDiligenceNeeded,
@dtOverDueDate = dtExpireddiligenceneeded
FROM tblDiligenceNeededDate
WHERE (dtDate = @startdate)

--SELECT @dtWarningDueDate AS 'dtWarningDueDate', @dtOverDueDate AS 'dtOverDueDate'

SET @SearchVal = LTRIM(RTRIM(@SearchVal)) + '%'

SELECT a.LoanNum AS [KCC Loan],
a.PoolNum AS [Pool],
CAST(CASE
WHEN parentLoan.LoanNum IS NULL THEN (0)
ELSE 1
END AS BIT) AS [Linked],
a.LoanStatus AS [Status],
a.AssetManager AS [Asset Manager],
a.AEExt AS [AM Extension],
NULLIF(a.LastUpDate, '') AS [Last Update],
a.BorrLast + COALESCE(', ' + nullIf(a.BorrFirst, ''),'') AS [Borrower Name],
a.BorrFirst AS [Borrower First Name],
a.BorrLast AS [Borrower Last Name],
a.BorrSSN AS [Borrower SSN],
a.PropertyCity AS [City],
a.PropertyState AS [State],
a.PropertyZip AS [Zip],
a.PropertyCounty AS [County],
m1.MainCategoryDesc AS [Exit Strategy],
NullIf(a.AssetExitDate,'') AS [Exp Asset Exit Date],
cast(isnull(a.BPOVal ,0) as decimal(20,2)) AS [Collateral Value],
NullIf(a.BPODate,'') AS [Collateral Value Date],
cast(isnull(ExitInfo.BidAmount,0) as decimal(20,2)) AS [Bid Amount],
cast((ExitInfo.GrossProceeds + a.AddlRevenue - ExitInfo.AcqCost - a.CarryCosts - ExitInfo.AddlAssetCost) as decimal(20,2)) AS [KCC Gain],
cast(isnull(ExitInfo.GrossProceeds,0) as decimal(20,2)) AS [Gross Expected Wire],
cast(isnull(ExitInfo.AcqCost,0) as decimal(20,2)) AS [Acquisition Cost],
NullIf(a.AcquisitionDate, '') AS [Acquisition Date],
cast(isnull(a.CurrentUPB,0) as decimal(20,2)) AS [Current UPB],
a.LienPosition AS [Lien],
a.AssetCoordinator AS [Asset Coordinator],
a.PortfolioMgr AS [Portfolio Mgr],
a.PMExt AS [PM Extension],
pd.FirstName + ' ' + pd.LastName AS [Portfolio Dir],
pd.Extension AS [Portfolio Dir Ext],
a.SellerLoanID AS [Seller Loan ID],
a.ServicerID AS [Servicer ID],
a.PropertyAddr1 AS [Address],
a.NoteType AS [1st TD Note Type],
a.CurrIntRate AS [1st TD Interest Rate],
cast(isnull(a.CurrBalance,0) as decimal(20,2)) AS [1st TD Balance],
cast(isnull(a.PandI,0) as decimal(20,2)) AS [1st TD Payment],
NullIf(a.FirstPayDate, '') AS [1st TD First Payment Date],
NullIf(Loan.LastPmtRecvdDate, '') AS [1st TD Last Payment Date],
NullIf(a.NextPayDate, '') AS [1st TD Next Payment Date],
a.OtherLienLoanNum AS [Concurrent Lien],
a.OtherLienIntRate AS [2nd TD Interest Rate],
cast(isnull(a.OtherLienBal,0) as decimal(20,2)) AS [2nd TD Balance],
cast(isnull(a.OtherLienPandI,0) as decimal(20,2)) AS [2nd TD Payment],
NullIf(a.OtherLastPay, '') AS [2nd TD Last Payment Date],
NullIf(a.OtherNextPay, '') AS [2nd TD Next Payment Date],
Audits.PurchFlag AS [Unclear to Purchase],
ExitInfo.InBKFlag AS [InBK],
a.BorrowerRepByAtty AS [Rep by Atty],
B.EndDate AS [NextPaymentDate], -- Kept_broke_date,
cast(isnull(B.PmtAmount,0) as decimal(20,2)) AS [NextPaymentAmount], --repay_monthly_pay,
case when IsNull(RepayStatus, 3) in (3,6) then 0 else 1 end AS [RepaymentLoan],
isNull(C.StatusName,'') AS [RepaymentBrokenOrKept],
case when B.EndDate < getDate() then 1 else 0 end AS [IsPlanCompleted],
B.EndDate AS [StopCodeEndDate],
TradeRequirements.ServTransfer AS [Service Transfer Date],
CASE
when (a.LoanStatus = 'Portfolio' and DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) <= @dtOverDueDate ) then 1
else 0
END AS [LastUpdateFlag],
CASE
WHEN (a.LoanStatus = 'Portfolio' and DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) <= @dtWarningDueDate and
DATEADD(dd, DATEDIFF(dd, 0, a.LastUpDate ), 0) > @dtOverDueDate ) then 1
ELSE 0
END AS [WarningUpdateFlag],
CASE
when (a.LoanStatus = 'Portfolio' and a.AssetExitDate < getdate()) then 1
else 0
END AS [ExpiredClosingDateFlag],
CASE
when (a.LoanStatus = 'Portfolio' and datediff (d, getdate(), a.AssetExitDate) > 0 and datediff (d, getdate(), a.AssetExitDate ) < 8 )then 1
else 0
END AS [UpcomingCloseFlag]
FROM dbo.KondaurData AS a
LEFT JOIN dbo.Audits on a.LoanNum = Audits.LoanNum
LEFT JOIN dbo.ExitInfo ON a.LoanNum = ExitInfo.LoanNum
LEFT JOIN dbo.exitstrategycategorymain m1 on exitinfo.ExitStrategyMainCategoryID = m1.MaincategoryID
LEFT JOIN dbo.Logins l on a.AssetManager = l.UserName
LEFT JOIN dbo.tblKon_PortfolioDirectors AS pd
ON l.PortfolioDirectorID = pd.PortfolioDirectorID
LEFT JOIN dbo.KondaurData parentLoan
ON a.LoanNum = parentLoan.OtherLienLoanNum -- is loan an other lien?
LEFT JOIN service.dbo.Loan
ON a.LoanNum = RIGHT(Loan.LoanID, 6)
LEFT JOIN service.dbo.RepayPlan B
ON a.LoanNum = RIGHT(B.LoanID,6)
AND (B.RepayStatus IN (1,3,4,5,6) )
LEFT JOIN service.dbo.RepaymentStatuses AS c
ON B.RepayStatus = C.Code
LEFT JOIN dbo.TradeRequirements
ON a.LoanNum = TradeRequirements.LoanNum
WHERE (a.LoanNum LIKE @SearchVal OR a.PoolNum LIKE @SearchVal OR a.Loanstatus LIKE @SearchVal OR a.PropertyAddr1 LIKE @SearchVal OR
a.PropertyCity LIKE @SearchVal OR a.PropertyState LIKE @SearchVal OR a.PropertyZip LIKE @SearchVal OR
a.LastUpDate = @datesearch OR a.AssetExitDate = @datesearch OR a.AssetManager LIKE @SearchVal OR
a.PortfolioMgr LIKE @SearchVal OR a.SellerLoanID LIKE @SearchVal OR a.ServicerID LIKE @SearchVal OR
pd.FirstName LIKE @SearchVal OR pd.LastName LIKE @SearchVal OR a.BorrLast LIKE @SearchVal OR a.BorrFirst LIKE @SearchVal)
ORDER BY a.LoanNum ASC;


quote:
Originally posted by visakh16

quote:
Originally posted by NguyenL71

Thank you All for helping me.
Have a good Weekend.

L


Does that mean your issue got sorted?
Can we see final working code then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-20 : 00:05:18
will work fine as long as a.LastUpDate and a.AssetExitDate fields have a timepart of 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-20 : 01:42:02
You need to explicitly indicate the conversion type either

SET @datesearch = CONVERT(DATETIME, @SearchVal, nnn)

or

SET DATEFORMAT mdy
SET @datesearch = CONVERT(DATETIME, @SearchVal)

otherwise you are at the mercy of the default, which may give you grief at some point.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site