Classic ASP with Oracle

Question

I have a select statement which is running fine in sqlplus but when i m trying to run in Classic ASP code I am getting below error

ORA-01843: not a valid month

My Select Query is as below

SELECT YEAR_MONTH, LAST_DAY(TO_DATE(TO_DATE('201106','YYYYMM'),'MM/DD/YYYY')) AS MAX_END_DT FROM MONTH_DIM WHERE '04-28-2016' BETWEEN MONTH_START_DATE AND MONTH_END_DATE

ASP Code as Below
Dim rs
cmd.CommandType = adCmdText
    cmd.CommandText =  "SELECT YEAR_MONTH, LAST_DAY(TO_DATE(TO_DATE('" & MAXYEARMONTH & "','YYYYMM'),'MM/DD/YYYY')) AS MAX_END_DT FROM MONTH_DIM WHERE '" &  END_DT & "' BETWEEN MONTH_START_DATE AND MONTH_END_DATE"
    set rs = cmd.execute --here is problem

is it something to do with NLS_DATE_FORMAT? I have checked in SQLPlus it show MM/DD/YYYY — LifeOfPi
– LifeOfPi, Commented Apr 28, 2016 at 12:40
What is the format for END_DT? Better use TO_DATE with your format fro END_DT. — Mottor
– Mottor, Commented Apr 28, 2016 at 12:50
And one TO_DATE is enough. LAST_DAY(TO_DATE('" & MAXYEARMONTH & "','YYYYMM')) — Mottor
– Mottor, Commented Apr 28, 2016 at 12:53
@Mottor, thank you for pointing out i have changed from MM-DD-YYYY to MM/DD/YYYY but with same error — LifeOfPi
– LifeOfPi, Commented Apr 28, 2016 at 12:55

Mottor · Accepted Answer · 2016-04-28 13:03:15Z

1

use TO_DATE('" & END_DT & "','MM/DD/YYYY')

answered Apr 28, 2016 at 13:03

Mottor

1,9583 gold badges16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Classic ASP with Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related