To display the day of the week of any date in the last 1,000 years

Asked

Viewed 150 times

1

I used this command

select to_char(to_date('18-apr-1972','dd-mon-yyyy'),'day') day from DUAL;

but gave error in execution:

ORA-01841: ano (completo) deve estar entre -4713 e +9999 e não pode ser zero
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

Could someone show me another way? I use Oracle.

sql oracle

  • Just remembering that in any system the counting base starts at 01-01-1970. At least microseconds started from that date.

  • 2

    @Williamasimilar to wrong Andean. That is the POSIX time, much of the Dbs use the Proleptic Gregorian Day, which starts in 4714 BC (indicated by -4713, because there is no year zero) - and even so, in Posix you can use negative numbers for dates prior to 1970. Also, Dbs that use Posix do not usually adopt microseconds in storage by default. The field is variant of INT normally.

  • @Bacco Boa! One that did not know. By this information nor delete my comment to include.

  • 1

    @Williamaparecidobrandino resolve to delete, just after signaling mine as obsolete (on the flag next to it), that moderation cleans later. If you want more details: https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar - is usually stored as Float in the Dbs, the fraction being the time, and the whole days. Example: Seventh grade, at noon, stay 7.5. In Posix they are already integers, being the number of seconds running since the date you mentioned (or before it if it is negative number) and it takes as a basis 86400 seconds every day (without Leap).

1 answer

1

That’s how it works:

select to_char(to_date('18-04-1972','dd-MM-yyyy'),'day') day from DUAL;

inserir a descrição da imagem aqui

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.