1

I'm writing an SQL statment that is supposed to do a count based on a date range. But, for some reason no data is being returned. Before I try and filter the count with my date range, everything works fine. Here is that code.

SELECT
CR.GCR_RFP_ID
,S.RFP_RECEIVED_DT
,CR.GCR_RECEIVED_DT
,CT.GCT_LOB_IND
FROM ADM.GROUP_CHANGE_TASK_FACT CT
JOIN ADM.B_GROUP_CHANGE_REQUEST_DIM CR 
    ON CR.GROUP_CHANGE_REQUEST_KEY = CT.GROUP_CHANGE_REQUEST_KEY 
JOIN ADM.B_RFP_WC_COVERAGE_DIM S 
    ON S. RFP_ID = CR.GCR_RFP_ID
WHERE CT.GCT_LOB_IND = 'WC'
AND CR.GCR_CHANGE_TYPE_ID IN ('10','20','30','50','60','70','80','90','100','110',
                          '120','130','140', '150','160','170','180','190','200',
                          '210','220','230','240','260','270','280','300','310',
                          '320','330','340','350','360','370','371','372')
AND S.RFP_AUDIT_IND = 'N'
AND S.RFP_TYPE_IND = 'A'

The date field I'm using is called CR.GCR_RECIEVED_DT. This is a new field a in the db and all the records are 01-JAN-00. But I'm still doing the count just to make sure I can grab the data. Now, I added this line:

AND CR.GCR_RECEIVED_DT LIKE '01-JAN-00'

just as a random test thing. I know all the dates are the same. And it works fine, no issues. So I remove that line and replace it with this:

AND CR.GCR_RECEIVED_DT BETWEEN '31-DEC-99' AND '02-JAN-00'

I used this small range to keep it simple. But even though 01-JAN-00 deffinetly falls between those two dates, no data is returned. I have no idea why this is happening. I even tried this line to:

AND CR.GCR_RECEIVED_DT = '01-JAN-00'

and I still don't get data returned. It only seems to work with LIKE. I have checked and the field is a date type. Any help wold be much appreciated.

Improve this question
6
  • 1
    Sounds like your date field is not a date data type but a string, that's why LIKE works and BETWEEN doesn't, between works with dates and numerics. (Well, technically it works with strings but not in the most meaningful of ways) Commented Aug 28, 2013 at 15:21
  • I thought that to. But i checked and it is a date type. Sorry, forgot to mention that. i just added it now though. Commented Aug 28, 2013 at 15:22
  • Still, try to use the function to_date('01-JAN-00','DD-Mon-YY') - you're just passing a string! techonthenet.com/oracle/functions/to_date.php Commented Aug 28, 2013 at 15:23
  • Not sure about oracle, in SQL Server some formats don't need explicit casting/converting, ie '20000101' any 'YYYYMMDD' will work as a string in a BETWEEN or other date comparison. Commented Aug 28, 2013 at 15:25
  • Is your NLS_DATE_FORMAT set to DD-MON-YY, by any chance? And are the table dates at midnight on 01-JAN-00, or do they have a time portion? Those two things together could explain what you see. Commented Aug 28, 2013 at 16:18

2 Answers 2

Reset to default
3

If your NLS_DATE_FORMAT is set to DD-MON-YY then the apparent discrepancy between the first two results can be explained.

When you use LIKE it implicitly converts the date value on the left-hand side to a string for the comparison, using the default format model, and then compares that to the fixed string; and '01-JAN-00' is like '01-JAN-00'. You're effectively doing:

AND TO_CHAR(CR.GCR_RECEIVED_DT, 'DD-MON-YY') LIKE '01-JAN-00'

Using LIKE to compare dates doesn't really make any sense though. When you use BETWEEN, though, the left-hand side is being left as a date, so you're effectively doing:

AND CR.GCR_RECEIVED_DT BETWEEN TO_DATE('31-DEC-99', 'DD-MON-YY')
  AND TO_DATE('02-JAN-00', 'DD-MON-YY')

... and TO_DATE('31-DEC-99', 'DD-MON-YY') is December 31st 2099, not 1999. BETWEEN only works when the first value is lower than the second (from the docs, 'If expr3 < expr2, then the interval is empty'). So you're looking for values bwteen 2099 and 2000, and that will always be empty. If your date model was DD-MON-RR, from the NLS parameter or explicitly via TO_DATE, then it would be looking for values between 1999 and 2000, and would find your records.

Your third result is a little more speculative but suggests that the values in your GCR_RECEIVED_DT field have a time component, or are not in the century you think. This is similar to the LIKE version, except this time the fixed string is being converted to a date, rather than the date being converted to a string; effectively:

AND CR.GCR_RECEIVED_DT = TO_DATE('01-JAN-00', 'DD-MON-YY')

If they were at midnight on 2000-01-01 this would work. Because it doesn't that suggests they are either some time after midnight, or maybe more likely - since you're using a 'magic' date in your existing records - they are another date entirely, quite possibly 1900-01-01.

Here are SQL Fiddles for just past midnight and 1900.

If the field will eventually have a time component for new records you might want to structure the condition like this, and use date literals to be a bit clearer (IMO):

AND CR.GCR_RECEIVED_DT >= DATE '2000-01-01'
AND CR.GCR_RECEIVED_DT < DATE '2000-01-02'

That will find any records at any time on 2000-01-01, and can use an index on that column if one is available. BETWEEN is inclusive, so using BETWEEN DATE '2000-01-01' AND '2000-01-02' would include any records that are exactly at midnight on the later date, which you probably don't want.

Whatever you end up doing, avoid relying on implicit conversions using NLS_DATE_FORMAT as one day it might not be set to what you expect, causing potentially data-corrupting or hard to find bugs; and specify the full four-digit year in the model if you can to avoid ambiguity.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you very much for your answer. i've been stressing this since this morning. big help
1

try something like this:

WHERE TRUNC(CR.GCR_RECEIVED_DT) = TO_DATE('01-JAN-00','DD-Mon-YY')

TRUNC without parameter removes hours, minutes and seconds from a DATE.

Improve this answer

4 Comments

This shouldn't affect the BETWEEN since op used a day before and day after the sample date, in a date equivalency check it's certainly a good point though.
i've already done this same thing using a different date field and it works fine. its goota be the field. as i said its a new field. all the records are 01-JAN-00. i used DISTINCT on the field to see if there are any others and the only othe thing that shows is null. but i did a count and it says there are 0 nulls. so i'm not even sure why its showing me a null
wonder what this would return: WHERE TRUNC(NVL(CR.GCR_RECEIVED_DT,'01-JAN-00')) = TO_DATE('01-JAN-00','DD-Mon-YY')
i looks like it either made all the dates null or returned the null values, but there shouldn't be null values

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.