Date type.

Question

Date type.

varunkum

Default port

Points: 1479
More actions
December 29, 2010 at 11:08 am

#232405

select ENTRY_DATE from CIRC.SUBS_TAG where ENTRY_DATE <> '10/27/2010'
I get "ORA-01843: not a valid month" error. ENTRY_DATE is date type. Why do i get this error. Please help me .Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

varunkum (12/29/2010)
select ENTRY_DATE from CIRC.SUBS_TAG where ENTRY_DATE <> '10/27/2010'
I get "ORA-01843: not a valid month" error. ENTRY_DATE is date type. Why do i get this error. Please help me .Thank you.

Can you run

exec SP_help [CIRC.SUBS_TAG]

and can you get the datatypes for each column

what is the excat error you are getting have here with error code also

like Msg:12345 .....

Thanks

Parthi

Thanks
Parthi

dbursey SSC Eights! Points: 831 More actions · Answer 2

Given the complexities of date data, I always do an explicit convert to a specific type, then do my compare - so with your example, I would say:

select ENTRY_DATEfrom CIRC.SUBS_TAG

where convert(varchar(10),ENTRY_DATE,101) <> '10/27/2010'

I'd also check my data to make absolutely sure that there are indeed rows meeting your criterion.

My .03 -- Donna B

pardeshkumar SSC-Addicted Points: 482 More actions · Answer 3

verify the format of date it is displaying (10-27-2010)

Mike John SSCertifiable Points: 7216 More actions · Answer 4

From that error message it looks like you are using Oracle and this is a SQL Server group. Either way changing to an unambiguous date format such as yyyymmdd will probably resolve it.

Mike

varunkum Default port Points: 1479 More actions · Answer 5

CREATE TABLE SUBS_TAG

(

ACCOUNT NUMBER(12) NOT NULL,

CODE VARCHAR2(2 BYTE) NOT NULL,

ENTRY_DATE DATE,

USERID_ENTRY VARCHAR2(10 BYTE),

USERID_LAST_WRITE VARCHAR2(10 BYTE),

SERVICE_TIME_OVERRIDE NUMBER(3),

TRANS_NUM NUMBER(12),

VALID_FROM DATE DEFAULT TO_DATE('01/01/1500','mm/dd/yyyy') NOT NULL,

VALID_UNTIL DATE DEFAULT TO_DATE('12/31/2999','mm/dd/yyyy') NOT NULL,

BEGIN_DATE DATE,

END_DATE DATE

)

Error is "ORA-01843: not a valid month".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

varunkum (12/29/2010)
CREATE TABLE SUBS_TAG
(
ACCOUNT NUMBER(12) NOT NULL,
CODE VARCHAR2(2 BYTE) NOT NULL,
ENTRY_DATE DATE,
USERID_ENTRY VARCHAR2(10 BYTE),
USERID_LAST_WRITE VARCHAR2(10 BYTE),
SERVICE_TIME_OVERRIDE NUMBER(3),
TRANS_NUM NUMBER(12),
VALID_FROM DATE DEFAULT TO_DATE('01/01/1500','mm/dd/yyyy') NOT NULL,
VALID_UNTIL DATE DEFAULT TO_DATE('12/31/2999','mm/dd/yyyy') NOT NULL,
BEGIN_DATE DATE,
END_DATE DATE
)
Error is "ORA-01843: not a valid month".

This is a Microsoft SQL Server forum, and you're using Oracle. May I suggest you post this in an Oracle forum (eg http://www.dbforums.com) as the majority of people here are not going to be familiar with Oracle

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass