Home » SQL & PL/SQL » SQL & PL/SQL » Hello, how to get last week DAY from sysdate
Hello, how to get last week DAY from sysdate [message #244794] Thu, 14 June 2007 02:35 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
Hello ,
Is there any functions to get "last week DAY"(exactly one week back) ie.
if today date is 14-06-2007 lastweek day ---->08(only day)
i hav used like the below, which is getting problem in the month ending and begining.

--->select (to_char(to_date(globtab_tbsdy,'YYYYMMDD'),'DD')-7) from globtab;

similarly is it possiblble to find "previous MONTH".

thx & rgds,
Ajay.
Re: Hello, how to get last week DAY from sysdate [message #244803 is a reply to message #244794] Thu, 14 June 2007 02:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have a look at the ADD_MONTHS built-in.

SQL> ALTER SESSION set nls_date_format='Dy DD/MM/YYYY';

Session altered.

SQL> SELECT sysdate today
  2       , add_months(sysdate,-1) prev_month
  3  FROM   dual
  4  /

TODAY          PREV_MONTH
-------------- --------------
Thu 14/06/2007 Mon 14/05/2007


MHE
Re: Hello, how to get last week DAY from sysdate [message #244810 is a reply to message #244803] Thu, 14 June 2007 02:59 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
but my date format in the system is "YYYYMMDD"

" it is showing the error
SELECT GLOBTAB_TBSDY today, add_months(GLOBTAB_TBSDY,-1) prev_month FROM globtab
*
ERROR at line 1:
ORA-01861: literal does not match format string "


is it possible to set our own date format such as YYYYMMDD
i mean with in the add_months()

[Updated on: Thu, 14 June 2007 03:00]

Report message to a moderator

Re: Hello, how to get last week DAY from sysdate [message #244811 is a reply to message #244794] Thu, 14 June 2007 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A preambule: End of week depends on your territory
The query is:
SQL> with 
  2    data as (
  3      select to_date('06/01/2007','MM/DD/YYYY')+rownum-1 dt
  4      from dual
  5      connect by level <= 15
  6    )
  7  select dt,
  8         dt-to_number(to_char(dt,'D')) eow_dt,
  9         to_char(dt-to_number(to_char(dt,'D')),'DD') eow_day
 10  from data
 11  /
DT          EOW_DT      EO
----------- ----------- --
01-JUN-2007 26-MAY-2007 26
02-JUN-2007 26-MAY-2007 26
03-JUN-2007 02-JUN-2007 02
04-JUN-2007 02-JUN-2007 02
05-JUN-2007 02-JUN-2007 02
06-JUN-2007 02-JUN-2007 02
07-JUN-2007 02-JUN-2007 02
08-JUN-2007 02-JUN-2007 02
09-JUN-2007 02-JUN-2007 02
10-JUN-2007 09-JUN-2007 09
11-JUN-2007 09-JUN-2007 09
12-JUN-2007 09-JUN-2007 09
13-JUN-2007 09-JUN-2007 09
14-JUN-2007 09-JUN-2007 09
15-JUN-2007 09-JUN-2007 09

15 rows selected.

My end of week is Saturday. Nothing to change in the query if yours is a different day.

Regards
Michel

[Updated on: Thu, 14 June 2007 03:01]

Report message to a moderator

Re: Hello, how to get last week DAY from sysdate [message #244812 is a reply to message #244810] Thu, 14 June 2007 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my date format in the system is "YYYYMMDD"

This is wrong. This is your display format, a date has no format.
But maybe your "date" is in fact a string? In this case use to_date to convert into a date.

Regards
Michel

[Updated on: Thu, 14 June 2007 03:03]

Report message to a moderator

Re: Hello, how to get last week DAY from sysdate [message #244816 is a reply to message #244794] Thu, 14 June 2007 03:09 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
yaa my date is a string format, is it prossible to get prev_month 'month' by using to_char()
Re: Hello, how to get last week DAY from sysdate [message #244818 is a reply to message #244816] Thu, 14 June 2007 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, apply to_date before.

Regards
Michel
Re: Hello, how to get last week DAY from sysdate [message #244828 is a reply to message #244818] Thu, 14 June 2007 04:09 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi michel,

i hav tried with to_date() but it is giving an error as

":literal does not match format string"
query:
select to_char(to_date(add_months(globtab_tbsdy,-1),'YYYYMMDD'),'MM')
from globtab

since i need only month.

thx & rgds,
Ajay

[Updated on: Thu, 14 June 2007 04:18]

Report message to a moderator

Re: Hello, how to get last week DAY from sysdate [message #244829 is a reply to message #244828] Thu, 14 June 2007 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should precise: apply to_date BEFORE add_months.

Regards
Michel
Re: Hello, how to get last week DAY from sysdate [message #244842 is a reply to message #244829] Thu, 14 June 2007 05:28 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi michel yaa i got the solution,

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(GLOBTAB_TBSDY, 'YYYYMMDD'),-1),'MM') "PREVIOUS_MONTH"
FROM GLOBTAB;


is there any functions to get last week date
ie.
say today date is 20070614 -----> 20070608
-->here only the date is reduced by one week.

thx a lot,
rgds,
Ajay
Re: Hello, how to get last week DAY from sysdate [message #244850 is a reply to message #244842] Thu, 14 June 2007 06:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Just like you did in your opening post: subtract 7 days from the date.

MHE
Re: Hello, how to get last week DAY from sysdate [message #244851 is a reply to message #244850] Thu, 14 June 2007 06:13 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
ok,
i wil try.
Thx & rgds,
Ajay
Re: Hello, how to get last week DAY from sysdate [message #244890 is a reply to message #244794] Thu, 14 June 2007 08:45 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Since no one else said it, NEVER use a string to store a date. Use a date column. Using a string to store a date is just plain bad design.
Re: Hello, how to get last week DAY from sysdate [message #244897 is a reply to message #244890] Thu, 14 June 2007 09:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think I mentioned that in a thread or two, yes. Very Happy

Good point to bring it up, Bill.

MHE
Re: Hello, how to get last week DAY from sysdate [message #245012 is a reply to message #244850] Thu, 14 June 2007 23:49 Go to previous message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
this the solution for getting exact previous week from specified date.


SELECT TO_CHAR(TO_DATE(GLOBTAB_TBSDY,'YYYYMMDD')
-7,'DD')
FROM GLOBTAB;

thx
Mhe

rgds,
Ajay

[Updated on: Fri, 15 June 2007 03:08]

Report message to a moderator

Previous Topic: how to use db link in procedure
Next Topic: ora 00913
Goto Forum:
  


Current Time: Sat Dec 10 22:20:47 CST 2016

Total time taken to generate the page: 0.04647 seconds