OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Date issues (Oracle, 11.2.0.4.0, Windows 7 Professional)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date issues [message #645263]

Tue, 01 December 2015 17:28

buggleboy007
Messages: 282
Registered: November 2010
Location: Canada

Senior Member

I have a question with DATE datatype at work:

Our tables have date column (RECORD_CREATE_TMS,MESSAGE_TMS) where the data type is defined as DATE. I am pasting the script of one such table:

CREATE TABLE sandeep_log
(
  LIGHT_TASK_MSG_LOG_ID  NUMBER(18)             NOT NULL,
  RECORD_CREATE_TMS      DATE                   NOT NULL,
  CREATE_USER_ID         VARCHAR2(8 BYTE)       NOT NULL,
  MSG_SVRTY_CD           VARCHAR2(20 BYTE)      NOT NULL,
  MSG_TMS                DATE                   NOT NULL,
  MSG_TXT                VARCHAR2(2000 BYTE)    NOT NULL,
  TASK_TYPE_CD           VARCHAR2(30 BYTE)      NOT NULL,
  DATA_SOURCE_CD         VARCHAR2(10 BYTE)      NOT NULL,
  LINE_NBR               NUMBER(7),
  MSG_NBR                NUMBER(18),
  PCKG_NM                VARCHAR2(30 BYTE),
  PRCDR_NM               VARCHAR2(30 BYTE),
  PRGRM_NM               VARCHAR2(30 BYTE),
  TABLE_NM               VARCHAR2(30 BYTE),
  RFRNC_ID               NUMBER(18),
  RFRNC_COLUMN_ID        NUMBER(18),
  TABLE_ID               NUMBER(18),
  MSG_STATUS_CD          VARCHAR2(11 BYTE)
)

The date format is stored as following:

11/27/2015 12:01:50 PM

However when I query to find out certain records for a specific date and use the following query, I am unable to retrieve any values despite the fact that there is data in the table.

The query that I am using is:

a)

select * from sandeep_log
WHERE record_create_tms = TO_CHAR(TO_date('11/28/2015','mm/dd/yyyy'),'mm/dd/yyyy');

Output encountered is: Not a valid month

b)

select * from sandeep_log
WHERE record_create_tms = TO_CHAR('11/28/2015','mm/dd/yyyy');

Output encountered is: Invalid number.

The only way I am able to work this one out is by passing the following query:

select * from sandeep_log
WHERE record_create_tms > trunc(sysdate)-3
order by record_create_tms asc;

THe problem with the above output is that it retrieves all the records from today till 28th November. I am only looking for 28th November records. Is there any way around it? Can any one help me?

NLS_DATE_FORMAT is set as DD-MON-RR

Thanks,
Sandeep

Report message to a moderator

Re: Date issues [message #645264 is a reply to message #645263]

Tue, 01 December 2015 18:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>WHERE record_create_tms = TO_CHAR(TO_date('11/28/2015','mm/dd/yyyy'),'mm/dd/yyyy');

above make NO SENSE since you start with a string, convert it to a DATE & then convert back to a string.
Remember that "=" means matches exactly; while DATE datatype contains a TIME component
>TO_date('11/28/2015','mm/dd/yyyy')
above defaults to '11/28/2015 00:00:00'

>WHERE record_create_tms = TO_CHAR('11/28/2015','mm/dd/yyyy');
NEVER use TO_CHAR on a string
NEVER use TO_DATE on a DATE

>WHERE record_create_tms > trunc(sysdate)-3

not as above but as below

WHERE record_create_tms between trunc(sysdate)-3 AND trunc(sysdate)-2

Now what hae you learned from all of this?

Report message to a moderator

Re: Date issues [message #645265 is a reply to message #645264]

Tue, 01 December 2015 18:25

buggleboy007
Messages: 282
Registered: November 2010
Location: Canada

Senior Member

Thanks a lot BS. Indeed in all seriousness I have learnt another way to manipulate DATE format. I was able to use TO_DATE using OE/HR schema and retrieve the results but this thought of Quote:

record_create_tms between trunc(sysdate)-3 AND trunc(sysdate)-2

never occurred to me.

and yes, you are right w.r.t:

Quote:

NEVER use TO_CHAR on a string
NEVER use TO_DATE on a DATE

As per Oracle manual and press book use TO_CHAR on date data type and TO_DATE on char data type.

Thanks again!

[Updated on: Tue, 01 December 2015 18:29]

Report message to a moderator

Re: Date issues [message #645271 is a reply to message #645263]

Wed, 02 December 2015 00:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

buggleboy007 wrote on Wed, 02 December 2015 04:58

The date format is stored as following:
11/27/2015 12:01:50 PM

No. Oracle does not store dates in the format you see. It stores it internally in 7 bytes with each byte storing different components of the datetime value.

Use TO_CHAR to convert the DATE into string to DISPLAY in your desired format. For date arithmetic, leave the date data type as it is. You need to compare date with date and not a string.

Also, since you are not concerned with the time element, as you need all the rows for a particular date, I would prefer using ANSI Date literal which uses a fixed format 'YYYY-MM-DD'. It is NLS independent.

WHERE record_create_tms >= DATE '2015-11-28' AND record_create_tms < DATE '2015-11-28' +1

As further reading, read this nice article by Ed.

Regards,
Lalit

[Updated on: Wed, 02 December 2015 00:15]

Report message to a moderator

Re: Date issues [message #645291 is a reply to message #645265]

Wed, 02 December 2015 07:05

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Quote:

The date format is stored as following:

No, it is not stored as you stated. DATE is stored in an internal binary format. What you showed is a character string representation of a DATE. One of many possible character string representations of the very same date.

----
Quote:

However when I query to find out certain records for a specific date and use the following query, I am unable to retrieve any values despite the fact that there is data in the table.

Despite the fact there is data in the table because your are trying to compare a DATE to a CHARACTER STRING.

--
Quote:

Indeed in all seriousness I have learnt another way to manipulate DATE format.

Then your "learnt" wrong.

Read the link provided by Lalit.

Report message to a moderator

Re: Date issues [message #645303 is a reply to message #645271]

Wed, 02 December 2015 09:20

buggleboy007
Messages: 282
Registered: November 2010
Location: Canada

Senior Member

@Lalith :

I did use TO_CHAR function to compare the date in my WHERE clause but it returned no/incorrect results. What baffled me was that the date column's datatype is DATE. So why did not the TO_CHAR work? Was it because the date was stored as "11/27/2015 12:01:50 PM" which as you say is a CHAR. Trust me the same TO_CHAR function when I used at home returned the results that I was looking for on native Oracle's OE schema.

Also the link of Ed Steven's "https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/" does not open. Keep's telling me that Quote:

This web page is not available
ERR_CONNECTION_RESET

Your statement Quote:

I would prefer using ANSI Date literal which uses a fixed format 'YYYY-MM-DD'. It is NLS independent.

suggesting that I use DATE literal is interesting. I used it and worked like a charm. New way of doing it.

Thanks again!

[Updated on: Wed, 02 December 2015 09:33]

Report message to a moderator

Re: Date issues [message #645305 is a reply to message #645303]

Wed, 02 December 2015 09:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

buggleboy007 wrote on Wed, 02 December 2015 20:50

@Lalith :

I did use TO_CHAR function to compare the date in my WHERE clause]

Nothing more to say than what I and others have already said, the date value displayed on your client has a format based on the NLS settings of your locale-specific NLS settings of the client.

Quote:

Also the link of Ed Steven's "https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/" does not open.

Sorry, but I don't have any problem with the link, unless you have some firewall blocking it or some restrictions at your workplace.

Quote:

suggesting that I use DATE literal is interesting. I used it and worked like a charm. New way of doing it.

Yes, it is really good and quite handy when you have no care for the time portion.

By the way, my name is Lalit and not Lalith Wink

[Updated on: Wed, 02 December 2015 09:43]

Report message to a moderator

Re: Date issues [message #645310 is a reply to message #645303]

Wed, 02 December 2015 14:21

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

buggleboy007 wrote on Wed, 02 December 2015 09:20

Also the link of Ed Steven's "https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/" does not open. Keep's telling me that Quote:
This web page is not available
ERR_CONNECTION_RESET

Works for me. Try just entering the base url (edstevensdba.wordpress.com). And navigate from there. If you get there you should see a menu bar with 'Presentations and Demos'. From there the second item listed is "But I want to store the date in format ...".

Report message to a moderator

Previous Topic:	get 7 working day excluding holidays in Oracle
Next Topic:	SQL update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 16:02:05 CDT 2024