Home » SQL & PL/SQL » SQL & PL/SQL » Date Column
Date Column [message #680292] Wed, 06 May 2020 01:22 Go to next message
deepakdot
Messages: 89
Registered: July 2015
Member
Hi,

Here is my scenario.

create table test (col1 date);
insert into test col1 values (current_timestamp);
commit;

Though this is a date column i am able to insert current_timestamp to that column. I want this Insert statement to fail.

Is there a way, If someone deliver this insert script, this insert will fail. If only date is passed like '2020-01-01', then it should success.

Thanks,
Deepak



Re: Date Column [message #680293 is a reply to message #680292] Wed, 06 May 2020 01:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
SQL is a strongly typed language, but Oracle will attempt implicit type conversions when bad programmers mis-match datatypes. This is what you are seeing. A conversion from timestamp to date is usually safe, your suggestion of a string to a date is often disastrous:
orclz>
orclz> create table test (col1 date);

Table created.

orclz> insert into test values ('2020-01-01');
insert into test values ('2020-01-01')
                         *
ERROR at line 1:
ORA-01861: literal does not match format string


orclz>
Re: Date Column [message #680294 is a reply to message #680292] Wed, 06 May 2020 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If only date is passed like '2020-01-01', then it should success.
But '2020-01-01' is NOT a date, it is a STRING.
So if this not a date but a string like this one you want to insert then you have to define the column as VARCHAR2 with a constraint for this format.
But if this a date, you have to specify why you don't want current_timestamp to be inserted as this is a valid date (or rather a timestamp but with an implicit conversion to a date).

Re: Date Column [message #680295 is a reply to message #680294] Wed, 06 May 2020 03:14 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
We need a date Column to ensure we are inserting a valid date. In date column we can not insert a invalid date or a string like '2020-01-35'.
But we don't need a time in that date column.

The DML scripts we deliver the value as '2020-01-01' which is good. but sometime developer by mistake deliver the value as "current_timestamp". So I wanted that to fail.

we have defaulted the format to be 'YYYY-MM-DD' .

insert into test col1 values ('2020-01-30'); -- This is Success now.
insert into test col1 values (current_timestamp); -- This is also Success but I want this to fail.

is there a way I can make current_timestamp to fail , if someone put in the insert script.
Re: Date Column [message #680296 is a reply to message #680295] Wed, 06 May 2020 03:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I see no hope. You appear to be determined to write code that is liable to fail at any time. Or timestamp.
Re: Date Column [message #680297 is a reply to message #680295] Wed, 06 May 2020 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
we have defaulted the format to be 'YYYY-MM-DD'
You but what about the client?
How can you be sure it will FOREVER be this one?
You must code in reliable way and not rely on external settings that can be modified.

If you require the time part does not exist (which means your code is not correct), you can create a trigger to remove it or add a constraint on the column that rejects all rows with a time part (in fact, with a time part of 00:00:00).

[Updated on: Wed, 06 May 2020 04:22]

Report message to a moderator

Re: Date Column [message #680298 is a reply to message #680295] Wed, 06 May 2020 04:56 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
deepakdot wrote on Wed, 06 May 2020 10:14
But we don't need a time in that date column.
So, you do not want COL1 column (DATE data type) to contain time component?

Is SYSDATE - valid date containing current time - considered to be valid date?

If not, simply add check constraint to ensure time component is empty in col1 (its value truncated to day is equal to actual column value).
Re: Date Column [message #680299 is a reply to message #680297] Wed, 06 May 2020 05:04 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
we have a after LOGON trigger which set NLS_DATE_FORMAT = 'yyyy-mm-dd';

Yes. I am looking for something like you suggested, to add a constraint to the date column. This is only for the development phase, to ensure by mistake we dont deliver value as 'current_timestamp'. if BY Mistake someone deliver as curent_timestamp , that should fail in development and corrected.
Re: Date Column [message #680300 is a reply to message #680299] Wed, 06 May 2020 05:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have to write decent code, that does not rely on implicit type casting. For example (only the beginning...):
orclz>
orclz> select to_date('2020-05-06','yyyy-mm-dd') from dual;

TO_DATE('2020-05-06
-------------------
2020-05-06:00:00:00

orclz> select to_date(current_timestamp,'yyyy-mm-dd') from dual;
select to_date(current_timestamp,'yyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


orclz>
Re: Date Column [message #680301 is a reply to message #680300] Wed, 06 May 2020 05:28 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
There are some restriction in our project to use to_date ...

The suggestion given by Michel Cadot and flyboy works for me , to have a constraint. This is just for the development phase to restrict mistake 'current_timesamp' delivery.

ALTER TABLE test ADD CONSTRAINT CHECK_DATE CHECK (COL1 = TRUNC(COL1)) ;
insert into test col1 values ('2020-01-01'); -- Success
insert into test col1 values (current_timestamp); -- Failed.

Thank you all for your quick suggestions.


Re: Date Column [message #680302 is a reply to message #680299] Wed, 06 May 2020 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If your code rely on the fact that there is no time part then the code is not correct and must be fixed.
If your code needs on the date part then it has to retrieve only this date part and does not rely on the fact there is no time part in the date column (and in the end there ALWAYS be a time part).
Something like this:
SQL> select trunc(sysdate) current_date, to_char(sysdate,'yyyy-mm-dd') current_date from dual;
CURRENT_DATE        CURRENT_DA
------------------- ----------
06/05/2020 00:00:00 2020-05-06
Re: Date Column [message #680303 is a reply to message #680302] Wed, 06 May 2020 05:48 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
We retrieve the data which is <= today . we can not use to_date , to_char functions. its restricted. else we just would have modify the select query to take the date part only.

Select * from test where col1 <= $DATE; (where the $DATE parameter is always today date, in YYYY-MM-DD) .

insert into test col1 values (current_timestamp);
select * from test where col1 <= '2020-05-06'; --> No Rows selected, because this contain time in it.

our standard is to put 'YYYY-MM-DD' in the DML. So i wanted, if by mistake someone put current_timestamp, it should fails. So that the DML can be modified and put only 'YYYY-MM-DD'.

So this check constraint Works.

ALTER TABLE test ADD CONSTRAINT CHECK_DATE CHECK (COL1 = TRUNC(COL1)) ;
insert into test col1 values ('2020-01-01'); -- Success
insert into test col1 values (current_timestamp); -- Failed.

Thank you so much.
Re: Date Column [message #680304 is a reply to message #680301] Wed, 06 May 2020 05:52 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
deepakdot wrote on Wed, 06 May 2020 12:28
There are some restriction in our project to use to_date ...

The suggestion given by Michel Cadot and flyboy works for me , to have a constraint. This is just for the development phase to restrict mistake 'current_timesamp' delivery.

ALTER TABLE test ADD CONSTRAINT CHECK_DATE CHECK (COL1 = TRUNC(COL1)) ;
insert into test col1 values ('2020-01-01'); -- Success
insert into test col1 values (current_timestamp); -- Failed.

Thank you all for your quick suggestions.
You do not need to use TO_DATE function to construct correct variable with DATE data type.

DATE literals may be used as well: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1

As the used string literals are in ISO format now, it is just four letters more, and your code becomes correct and independent on any NLS environment change:
insert into test(col1) values (date '2020-01-01');
(I wonder how does the code 'success' with your syntax)
Re: Date Column [message #680306 is a reply to message #680303] Wed, 06 May 2020 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
select * from test where col1 <= '2020-05-06'; --> No Rows selected, because this contain time in it.
This is what I say, the code is bad.
It should be something like:
select * from test where col1 <= date '2020-05-06' + 1/86400
select * from test where col1 < date '2020-05-06' + 1
And surely not rely on a date format which can be change at any time.

Re: Date Column [message #680308 is a reply to message #680306] Wed, 06 May 2020 08:00 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Just add a check constraint

CREATE TABLE TEST
(
  COL1  DATE
);

ALTER TABLE TEST ADD (
  CONSTRAINT CHECK_FOR_TIME_ELEMENT
  CHECK (COL1 = TRUNC(COL1))
  ENABLE VALIDATE);
*** TESTING ***

INSERT INTO Test Col1
VALUES (CURRENT_TIMESTAMP);


ORA-02290: check constraint (WHB.CHECK_FOR_TIME_ELEMENT) violated
Re: Date Column [message #680311 is a reply to message #680308] Wed, 06 May 2020 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@Bill, OP said he did it and it fulfilled his needs.
The question addressed in the latest posts is now why does he (wrongly) need it.

Re: Date Column [message #680312 is a reply to message #680311] Wed, 06 May 2020 08:16 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Sorry I missed that. Thanks Michel
Re: Date Column [message #680313 is a reply to message #680303] Wed, 06 May 2020 08:20 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
deepakdot wrote on Wed, 06 May 2020 05:48
We retrieve the data which is <= today . we can not use to_date , to_char functions. its restricted. else we just would have modify the select query to take the date part only.
.
Why are you restricted from using the best possible solution?
Re: Date Column [message #680340 is a reply to message #680313] Fri, 08 May 2020 02:53 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
The same codebase is used for all DBMS. Only ISO format / functions are allowed. using 'date' in sql may be an option as Michel said, But thats a big change adding to all sqls in the application.

Yes. as i said, I (wrongly) need it .. Only in the development env. Smile
Re: Date Column [message #680347 is a reply to message #680340] Fri, 08 May 2020 09:09 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
[quote title=deepakdot wrote on Fri, 08 May 2020 02:53]The same codebase is used for all DBMS. Only ISO format / functions are allowed. using 'date' in sql may be an option as Michel said, But thats a big change adding to all sqls in the application.

But is it too big a change to make your code reliable?

The idea of database-agnostic code is a myth.
Re: Date Column [message #680372 is a reply to message #680347] Sat, 09 May 2020 09:48 Go to previous messageGo to next message
deepakdot
Messages: 89
Registered: July 2015
Member
The idea of database-agnostic code is a myth. >> this is NOT a myth. our product is running successfully successfully in 3 DBMS real production world.
Re: Date Column [message #680373 is a reply to message #680372] Sat, 09 May 2020 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is, if I could have your code I'd show you cases where the results will be different for different RDBMS.
Try to get T. Kyte's Expert Oracle Database Architecture book, in its first chapter (iirc) Tom details an example of an application which behave differently between Oracle and SQL Server with the same code. And, above all, he explains why it is not possible to always get the same results in both RDBMS due to their principles of transaction isolation levels, concurrency and locking.

[Updated on: Sat, 09 May 2020 12:14]

Report message to a moderator

Re: Date Column [message #680374 is a reply to message #680372] Sat, 09 May 2020 12:11 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
deepakdot wrote on Sat, 09 May 2020 09:48
The idea of database-agnostic code is a myth. >> this is NOT a myth. our product is running successfully successfully in 3 DBMS real production world.
I didn't say you coulnd't write an application in such a way. I'm saying the idea of completely agnostic code is a myth. How do you deal with incompatible data types across rdbms products?
Re: Date Column [message #680375 is a reply to message #680292] Sat, 09 May 2020 12:26 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
deepakdot wrote on Wed, 06 May 2020 01:22
Hi,

Here is my scenario.

create table test (col1 date);
insert into test col1 values (current_timestamp);
commit;

Though this is a date column i am able to insert current_timestamp to that column. I want this Insert statement to fail.

Is there a way, If someone deliver this insert script, this insert will fail. If only date is passed like '2020-01-01', then it should success.

Thanks,
Deepak



Why do you need it to fail? Oracle will properly cast it to a DATE. And in oracle DATE datatypes always, by definition, include a time component, resolved down to the second:

SQL> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
09-may-2020 12:23:02

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
09-may-2020 12:23:30

SQL> select trunc(sysdate) from dual; 

TRUNC(SYSDATE)
--------------------
09-may-2020 00:00:00

You can always set the time to 00:00:00 by use of TRUNC. Or just ignore the time, if you are trying to store that in a different column. Just another example of different data types for the "same" use, in different rdbms products.
Re: Date Column [message #680377 is a reply to message #680373] Sat, 09 May 2020 12:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Tom details an example of an application which behave differently between Oracle and SQL Server with the same code.
Another IIRC, the issue TK highlighted was to do with NULL. SQL Server used to allow comparisons with NULLs. So code such as

SELECT ... WHERE SOMETHING = NULL;

could return rows. I think (another IIRC) SQL Server has fixed that in later releases. All to do with ternary logic, of course. It is a long time since I had to study such things.
Re: Date Column [message #680379 is a reply to message #680377] Sat, 09 May 2020 13:31 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Another IIRC, the issue TK highlighted was to do with NULL.
The point he emphasized in the chapter I mentioned was the differences due to differences in transaction isolation level and locking (like read locks in SQL Server vs multiversioning in Oracle - although SQL Server now also supports multiversioning but this is not the default behavior).

Previous Topic: Configuring Scripts
Next Topic: Displaying select results in double columns
Goto Forum:
  


Current Time: Thu Mar 28 04:59:35 CDT 2024