TSQL error

noetsi

No cake for spunky
#1
I am running

Select c.CUSTOMERID, c.DateOffWaitingList,c.DATEBEGIN_14,a.SAMASDATE_1, c.CASENUMBER, c.DATEBEGIN_AT_CLO, TRANSAMOUNT_PAID
From dbo.RPTAuthorization as A

Join dbo.RPTCaseSummary as c on a.customerid=c.customerid AND c.extractdate=a.extractdate AND c.casenumber=a.casenumber

Where a.EXTRACTDATE ='8/31/2015' and a.SAMASDATE_1 >= c.DATEBEGIN_14 and c.DateOffWaitingList between '1/01/2015' AND '1/31/2015 59:59:000'
and SEVERITYDISABILITY ='1'

and getting
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Which makes no sense at all. I am not converting anything. I am just pulling in existing data from a table.
 

bryangoodrich

Probably A Mammal
#2
First, I see 2 possible areas where a conversion could exist

Code:
Select c.CUSTOMERID, c.DateOffWaitingList,c.DATEBEGIN_14,a.SAMASDATE_1, c.CASENUMBER, c.DATEBEGIN_AT_CLO, TRANSAMOUNT_PAID
From dbo.RPTAuthorization as A

Join dbo.RPTCaseSummary as c on a.customerid=c.customerid AND [COLOR="red"]c.extractdate=a.extractdate[/COLOR] AND c.casenumber=a.casenumber

Where a.EXTRACTDATE ='8/31/2015' and a.SAMASDATE_1 >= c.DATEBEGIN_14 and [COLOR="red"]c.DateOffWaitingList between '1/01/2015' AND '1/31/2015 59:59:000'[/COLOR]
and SEVERITYDISABILITY ='1'
I would review the table definition for dbo.RPTCaseSummary to ensure that DateOffWaitingList actually is a datetime variable since your WHERE clause is looking for values between a datetime range. In fact, thinking about it now, I've never used a BETWEEN clause for a datetime. I'd double-check that or rewrite the query to (1) be consistent on the end points--include times on both of them--and (2) use equality checks instead of BETWEEN. Additionally, you have 3 zeros on your end-time, which may be treating it as a string and incorrectly converting it?

Try this

Code:
SELECT 
    C.CUSTOMERID
    ,C.DateOffWaitingList
    ,C.DATEBEGIN_14
    ,A.SAMASDATE_1
    ,C.CASENUMBER
    ,C.DATEBEGIN_AT_CLO
    ,TRANSAMOUNT_PAID
FROM dbo.RPTAuthorization A
INNER JOIN dbo.RPTCaseSummary C 
    ON  A.customerid  = C.customerid 
    AND A.extractdate = C.extractdate 
    AND A.casenumber  = C.casenumber
WHERE A.EXTRACTDATE = '8/31/2015' 
AND   A.SAMASDATE_1 >= C.DATEBEGIN_14 
AND   C.DateOffWaitingList >= '1/01/2015 00:00:00' 
AND   C.DateOffWaitingList <= '1/31/2015 59:59:00'
AND   SEVERITYDISABILITY = '1'
 

noetsi

No cake for spunky
#3
They both were datetime fields which I knew when I ran it. We use between statements with date time fields a lot and they work.

The error was simple and embarrassing... I wrote the date wrong. It should have been ....23:59:59.00' I left off the hour. I usually do the date time between statements by pulling in the day after the extract (say Sept 1 for an august extract) because I know Sept 1won't be in the August extract and will pull in the last unit in the extract correctly. Here I could not do that.