I have a query and for some reason my date is not letting the records pass thorugh to the results.
Below is the snippet where i do get successful results
--RESULTS CUST_ACCT_ID CNTRCT_LEAD_ID BEG_DT END_DT 419162 124740 2010-12-14 00:00:00.000 2011-01-31 00:00:00.000 419162 208709 2010-12-14 00:00:00.000 2011-01-31 00:00:00.000
Now fast forward when i try to use in a sproc with other tables this result is not there...
I forgot to ask the perhaps most relevant question as a response: What do you get when you ommit the BETWEEN AND part of the query?
Why do you cast your datetime-variable to a char(10)? If you want it as a char(10), why not make it a char(10) to begin with?
Are the E.BEG_DT and E.END_DT columns datetime columns or char(10) columns? If they are char() columns, what's their format? If you want YYYY-MM-DD you should pass 120 as the last parameter to convert function.
Before the query, after assigning a value to @BEG_DT do:
That's to check that you don't run into problems with your date-input, due to local/regional settings. The format YYYYMMDD hh:mm:ss.nnn (eg 20101201 00:00:00.000) always works if you want to assign a string value to a datetime-column, it's independent of regional settings.
Values passed in by SSRS should be in date in order to use the calandering....
I'm making progress but the effective date was 12-14-2010 and if i use a date range that encompasses the whole month - i do not understand why it is not being picked up.
SELECT DISTINCT r.CUST_ACCT_ID AS ACCT_NUM, r.PRI_ACCT_IND AS PRIMARY_ACCOUNT, r.CUST_NUM AS PRI_ACCT_NUM, r.CUST_ACCT_NAM, r.ASAP as ASAP_Enrolled, r.OS_ACCT, MAX(CASE WHEN (rtl.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS RTL_ACCT, MAX(CASE WHEN (ostp.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS ONESTOP_LEAD,
FROM REFERENCE.dbo.T_DCON_CONT_ELIG_HIST e LEFT JOIN REFERENCE.dbo.t_IW_CUST_ACCT r ON e.CUST_ACCT_ID = r.CUST_ACCT_ID
WHERE r.CUST_CHN_ID = 512
GROUP BY r.CUST_ACCT_ID , r.PRI_ACCT_IND , r.CUST_NUM , r.CUST_ACCT_NAM, r.ASAP, r.OS_ACCT, OSTP.PRTY_CONT, lvnx.PRTY_CONT, carve.PRTY_CONT
ORDER BY 1
answered Jan 31, 2011 at 03:31 PM