Create Date variable, Oracle

Question

I am trying to create a variable that I can use to compare a date column in one of my tables. I know for a fact that this date exists but I am not getting any results but no error messages either. Here is what I have:

VAR TEST_DATE Char(10)
EXEC SELECT '06/20/1989' INTO :TEST_DATE FROM DUAL;
SELECT LASTNAME, FIRSTNAME
FROM MEMBER
INNER JOIN PERS ON MEMBER.ID = PERS.ID
WHERE PERS.DATEBIRTH = TO_DATE(:TEST_DATE,'MM/DD/YYYY');

Thanks.

Is there a record which has DOB as 06/20/1989. Try using > or < operator to see if any record are coming — Jigar
– Jigar, Commented Dec 29, 2014 at 17:49
I got results using: or Pers.DateBirth = '06/20/1989'. So does that mean that there is something wrong with my variable format? If so, I can't point it out. — Maximillion
– Maximillion, Commented Dec 29, 2014 at 17:57
Is the column in your table of type DATE, or is it a string? Do you actually have a matching record in MEMBER? — Alex Poole
– Alex Poole, Commented Dec 29, 2014 at 18:19
If you got the results with condition Pers.DateBirth = '06/20/1989' then I suspect that Pers.DateBirth is of type String not Date. So in order for your above query to work convert Pers.DateBirth to Date format using TO_DATE(Pers.DateBirth,'MM/DD/YYYY'). Try and let me know if working — Jigar
– Jigar, Commented Dec 29, 2014 at 18:29
Thanks for your help all, but I found out that I kept hitting 'Run Statement' when I had to use 'Run Script'...sorry. It works fine, I'm just new to Oracle. I can already tell I am going to drive myself crazy with the little differences. — Maximillion
– Maximillion, Commented Dec 29, 2014 at 18:53

tvCa · Accepted Answer · 2014-12-29 19:05:14Z

1

If DATEBIRTH contains hours/minutes, this is how to compare with date only type of DATE variables:

SELECT LASTNAME, FIRSTNAME
FROM MEMBER
INNER JOIN PERS ON MEMBER.ID = PERS.ID
WHERE TRUNC(PERS.DATEBIRTH) = TO_DATE(:TEST_DATE,'MM/DD/YYYY');

answered Dec 29, 2014 at 19:05

tvCa

8267 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Create Date variable, Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related