Date Column [message #680292] |
Wed, 06 May 2020 01:22 |
|
deepakdot
Messages: 91 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 |
John Watson
Messages: 8938 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 #680296 is a reply to message #680295] |
Wed, 06 May 2020 03:19 |
John Watson
Messages: 8938 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 |
|
Michel Cadot
Messages: 68665 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 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
deepakdot wrote on Wed, 06 May 2020 10:14But 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 #680300 is a reply to message #680299] |
Wed, 06 May 2020 05:10 |
John Watson
Messages: 8938 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 #680304 is a reply to message #680301] |
Wed, 06 May 2020 05:52 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
deepakdot wrote on Wed, 06 May 2020 12:28There 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 #680308 is a reply to message #680306] |
Wed, 06 May 2020 08:00 |
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 #680373 is a reply to message #680372] |
Sat, 09 May 2020 11:14 |
|
Michel Cadot
Messages: 68665 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 #680375 is a reply to message #680292] |
Sat, 09 May 2020 12:26 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
deepakdot wrote on Wed, 06 May 2020 01:22Hi,
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 |
John Watson
Messages: 8938 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 |
|
Michel Cadot
Messages: 68665 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).
|
|
|