CODEWITHSUNDEEP
sqloracle-database
user3586248
user3586248

Reputation: 163

Oracle sql not grabbing day for the current date

I am trying to get the day of the week for the asofdate field. I am receiving an error using Oracle SQL that states: 'a non numeric character was found where a numeric character was expected.' That error is for this query:

select to_char(to_date(max(distinct(asofdate)), 'mm/dd/yyyy'), 'DY') from PS_Z_EXS251AE_EMP

the below query returns '1/6/2015'

select max(distinct(asofdate)) from PS_Z_EXS251AE_EMP

Does anyone know what I'm doing wrong?

Upvotes: 0

Views: 49

Answers (2)

Jean-François Savard
Jean-François Savard

Reputation: 21004

You can simply do

select distinct max(extract(day from asofdate)) from from PS_Z_EXS251AE_EMP;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If asofdate is stored as a date/time data type, then why are you converting it to a date. Also, why are you using max():

select to_char(asofdate, 'DY')

If asofdate is stored as a string, then you should probably fix the data. Why are you storing a date as a string. That is the wrong type.

If you want the day of the week of the maximum date, which your question suggests, then just do:

select to_char(max(asofdate), 'DY')

Upvotes: 1

Related Questions