how to search for a date row in sql?

user_13681708 0
Viewed 150

I create column in this way:

ALTER TABLE cages
ADD test date;

I add date to this column

for instance

'01/07/21'

and when I use like always select:

select *
from cages
where test = '01/07/21';

I get nothing, it's weird, because in different table it's works... Can it connection with pk or fk or what is the reason of this?

edit:

I use SQL orlace developer.

enter image description here

edit:

thanks everyone for help, problem was that I used calendar to put date to column and it add date with time.

Why is it possible, when I have type date not dateTime?

oracle sql
4 Answers

Not everyone formats date values the same way. When looking at a date like 01/07/21, most of the people on this site will naturally read January 7, 2021*. The group that reads this as July 1, 2021 (today) is significant, but still slightly smaller. A few people come from cultures where July 21, 2001 is the natural interpretation.

To avoid this kind of ambiguity, when writing date literals for SQL you should always format them using the ISO-8601 formats, which always uses four digit years, goes in sequence from most significant term on the left to least significant term on the right, and always uses leading zeroes to fill out the full width of a term:

yyyy-MM-dd HH:mm:ss.fff
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
yyyyMMdd (unseparated version of the format preferred on Sql Server for date-only values for historical reasons)

Anything else is wrong for SQL.

For completeness, I also want to key in on the word "literals" from the beginning of the second paragraph. We should always use parameterized queries/prepared statements when putting date values into a query from a client code language, rather than using string manipulation to substitute a literal into the SQL command. On strongly typed platforms this usually means using the DateTime type provided by the language to set the value. If you find yourself converting a datetime variable to a string for inclusion in an SQL query, you're making a mistake.

* This isn't just a blind assertion. A few years back I did a basic review of the public portion of the Stack Overflow developer survey, where I first looked up which countries/languages default to which date formats, and then grouped countries together based on their format. I wish I had saved the results :/. I forget how I treated places with mixed heritage like Canada.

Joel_Coehoorn_30430

Your root problem, and I'm amazed no one seems to have picked up on this, is that your column is a DATE but in your query you are comparing it to a STRING. This may or may not work, depending on your NLS_DATE_FORMAT setting. You need to compare like data types:

select *
from cages
where test = to_date('01/07/21','dd-mm-yy');

I leave it as an exercise for the student to go to the SQL Reference manual and read up on the TO_DATE function.

I also beg and plead with you to not be trying to use 2-digit years. As an industry we were supposed to have solved that problem over 20 years ago. Does the term "Y2k bug" not mean anything to you?

As it is, the date that is represented by the string '01/07/21' could be understood to be any of the following

  • Jan 7
  • Jan 21
  • Jul 1
  • Jul 21

And who knows the year? 2021? 1921? 1821? 2001? 1901? 2007? 1907?, 1807?

You might want to read this.

user_57325370

Using SQL Developer if you insert a date in a table using user interface it automatically generates hours and minutes like if it's a Timestamp. If the format you are using is correct you should be able to retrieve your rows using like operator instead of equal.

select * from cages where test like '01/07/21%';

The only way to retrieve your rows using equal operator is when the timestamp is set to 00:00:00

user_107220880

1st July 2021 is written date '2021-07-01' in Oracle SQL.

You can read more about literals in the Oracle SQL Reference.

You can also use a to_date expression like

to_date('2021-07-21','YYYY-MM-DD')

or

to_date('1-Jul-21', 'DD-Mon-YY', 'nls_date_language = English')

or indeed

to_date('1-Lug-21', 'DD-Mon-YY', 'nls_date_language = Italian')

but frankly, why would you?

Bear in mind that that the person running the query/report/procedure, or the application server in use, may not have the same territory and language settings as you, so it is dangerous to assume that the century for a 2-digit year will always be what you expect (what year is '50'?) or the language will always be English, or the week always starts on a Monday. I worked on a system once where we deployed some code that used 'DD-MON-YYYY', to offices in London and Paris. We deployed it in September, and we had a production issue in Paris in February, because Sep, Oct, Nov, Dec and Jan still worked, but French has no Feb.

user_2304710
Related