Format check on varchar check!! [message #637411] |
Sun, 17 May 2015 06:31 |
|
kaltiimaar
Messages: 6 Registered: May 2015
|
Junior Member |
|
|
i m trying to check mm/dd/yyyy format on varchar datatype column??
i have tried below query... in below query a2 is varchar2 datatyoe column...
example:
select a2,case when (regexp_like(a2,'(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d') or
regexp_like(a2,'([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d') or a2 is null ) then 'valid' else 'invalid' end validd
from TEST_DATATYPE;
a2 validd
12/03/2006 valid
2006/13/02 invalid
2006/12/12 invalid
2006/12/12 invalid
13/13/2006 valid
13/13/20 invalid
3/3/2006 valid
03/3/2006 valid
3/32/2006 invalid
13/13/2152 invalid
15/3/2006 valid
15/3/2006 valid
23/13/2152 invalid
Here i want second last value to be invalid as well, because it is not valid date???
can anyone help on this??
|
|
|
Re: Format check on varchar check!! [message #637412 is a reply to message #637411] |
Sun, 17 May 2015 06:45 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
DATE doesn't have any format, what you see is for display so that it could be interpreted by humans. It is stored in an internal format by Oracle in 7 bytes.
It is a bad idea to do what you are trying to do. Store them as DATE and not VARCHAR.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Format check on varchar check!! [message #637428 is a reply to message #637423] |
Sun, 17 May 2015 09:55 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:because if we define a column to date datatype, the default format is mm/dd/yyyy..
There is no format to a DATE datatype.
YOU define the format you want to use it.
Examples:
SQL> select sysdate from dual;
SYSDATE
-------------------
17/05/2015 16:53:10
MY default format is not yours.
SQL> alter session set nls_date_format='MM/DD/YYYY';
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
05/17/2015
Now it is the same than yours.
But at any time SYSDATE as an internal format we don't care, and this is also true for internal format of a stored DATE data.
|
|
|