SQL & PL/SQL :: Change Week Startday To Tuesday

Jan 16, 2012

select trunc(to_date('1201090000', 'yymmddHH24mi'),'DAY') from dual

will give output

08-jan-2012 00:00:00

which is sunday as per AMERICA nls_territory setting.

For my requirement i want to group from tue-mon for a week instead of sun-sat. modify the query to use the week start day as Tuesday.

View 2 Replies


ADVERTISEMENT

PL/SQL :: Change Monday Like First Day Of Week

Oct 18, 2012

I need to change Monday like first day of week and not Sunday.

select to_char(to_date('01-01-2012'),'w') from dual

Result: 1

select to_char(to_date('02-01-2012'),'w') from dual

Result:1

and the idea is that 02-01-2012 must be the second week

View 3 Replies View Related

SQL & PL/SQL :: First Tuesday Date Between Two Parameter

Nov 20, 2010

i need to get first Tuesday per each month between two parameter i will use cursor in forms.

for example

my parameter is '04122007' and '31122009'
i need the first tuesday for each month
like '02122008'
'01�12009'
'03�22009' ext....

View 8 Replies View Related

Performance Tuning :: Materialized View Which Should Refresh Every Tuesday And Friday At 5am

Mar 20, 2012

I need to create a Materialized View, which should refresh every Tuesday and Friday at 5am.

CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;

I can do it for single day like above, but how to refresh on multiple days, using create materialized view syntax .

View 2 Replies View Related

SQL & PL/SQL :: Count Of Updates By Week

Feb 19, 2013

I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:

Record Date_Modified
155291141/23/2013
157277201/24/2013
152619321/25/2013
142263211/28/2013
140043421/28/2013
150050431/28/2013
148315761/29/2013
154364281/31/2013
148066382/1/2013

[Code]...

I would like to return:

Week Updates
43
57
69
77
84

Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.

how to go about this task.

View 7 Replies View Related

SQL & PL/SQL :: Week On Particular Day According To Arabic Calendar

May 26, 2013

As my requirement is that to find week for particular date according to Arabic calender.As per Arabic calender first day for year starts from saturday.

so when i execute query :

select to_char(to_date('05/26/2013','MM/DD/RRRR'),'WW')
from dual;

Then it gives 21,but as per arabic calender it should show 22nd week.

getting result according to Arabic calender as it should return 22,because weak starts from saturday according to Arabic calender.

View 6 Replies View Related

PL/SQL :: How To Get Monday Of Every IW Week In A Year

Jun 29, 2012

I had to get a query to give me all the Mondays of a IW week in a year. I think as per the IW weeks there are around 52.1 weeks in a year. So basically, I want the starting Monday of every week. I would then store those values in a variable and use them in APEX as column headers. Online I saw some samples not related with this, but where looping was done using the all_objects. Not sure if that is the right approach.

The problem is since this is not stored in any table, how can I loop like 52/53 times such that the output shows all the Mondays (date format) for a given year. Basically, the output would be 52/53 rows with a date(Monday of the week).

View 10 Replies View Related

To Restore Database To Last Week

Dec 31, 2012

I am running 10g on Linux, and last week someone messed up a database and it needs to be restored to 27 December. I am not a DBA, out regular DBA is on vacation till mid January,

I know we have archivelog on (I can see .arc files for the database), and I also have a backup of the actual data files and control files from when the database was first created. So in other words, I have all archive files and the initial data files. What i do not know is how to set the restore till December 27.

View 8 Replies View Related

Date Search By Last Week / Month?

Nov 2, 2011

I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.

What I need is one for month end and one for last 7 days.

here's what I have so far.

select ap.name

to_char(ap.opendate,'MM-DD-YY') "Open Date"

from [databasname]

where ap.opendate between databasename-7 and databasename-1

I really don't want to change the date myself I want the system to know when the 7 days or month is.

View 4 Replies View Related

SQL & PL/SQL :: Find Start And End Date Of Week?

Feb 5, 2011

I am creating report for this i want to make one query..

Query like this

My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...

Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...

View 10 Replies View Related

SQL & PL/SQL :: Get Date From Year / Week And Weekday

Apr 5, 2011

How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.

View 8 Replies View Related

SQL & PL/SQL :: Display 1st Day Of Week Within Date Range

Jul 14, 2010

How to display date every monday within date range

DEsc start date end date
XXXX 1/2/2010 31/10/2010

output
date every monday

View 25 Replies View Related

SQL & PL/SQL :: Get First Week Starting With First SUNDAY Of Year

Nov 9, 2012

I want to get the week of the year.

Conditions are:

1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013)
2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)

At many place I found the below solution:

select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;

But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).

View 6 Replies View Related

SQL & PL/SQL :: Find Total Sales For The Whole Last Week

Mar 7, 2010

I have two columns in table

sales_date
sales_amount

I need to find total sales for the whole last week.

Today is current week i.e. 1 (March 1, 2010 and March 7, 2010)

I need to find total_sales for the last week (i.e. Feb 22, 2010 to Feb 28, 2010)

I am unable to create logic for the same.

View 4 Replies View Related

PL/SQL :: Selecting Certain Day Of Week From Range Of Dates

Nov 21, 2012

I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.

column 1: count of customers, count(customer_id)
column 2: Date

Not even sure if this is possible to select a date using the day name ?

Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .

View 4 Replies View Related

SQL & PL/SQL :: Show Week Days Without Friday And Saturday

Oct 25, 2011

how can create function or sql statement to show week of days without Friday and Saturday

View 13 Replies View Related

SQL & PL/SQL :: Display Week Number As Per Financial Year (Apr - Mar)

Dec 9, 2012

I hava an requirement to get week number from particular date as per indian financial year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.

But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,

Date week_no
apr 01 to 07 1
apr 08 to 14 2
apr 15 to 21 3
.
.
.
.
.
Next year mar - 31 n

how to do this am waiting for your reply.

View 4 Replies View Related

Reports & Discoverer :: Week Day Starts From Monday

Feb 20, 2012

I have m display financial year calender(April-2011 to March 2012) and i want to display monday as a first week day.

View 3 Replies View Related

SQL & PL/SQL :: Schedule Procedure On Days Of Week At Specific Time?

Apr 12, 2010

I need to schedule a procedure on SUN, MON,TUE, WED and THU at 6:30 AM. make sure the folloiwng code is o.k.

begin
dbms_scheduler.create_job
(job_name => 'Load-Data_Calc',
job_type => 'STORED_PROCEDURE',

[code]...

View 5 Replies View Related

SQL & PL/SQL :: How To Record / Nested Table Type Based On Row Returned By Week

May 10, 2011

Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e

TYPE RC IS REF CURSOR;
C2 RC;
Type t is table of c2%rowtype;
Following is some more explanation of what I am trying to do.

I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like

1 Select * from emp
2 Select count(1) from dept

Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.

View 3 Replies View Related

Field Constraint For Days Of Week Using Single Letter Representation

Jul 6, 2013

I know I can add a check constraint for m,t,w,r,f,s,u How can I add a constraint that will allow any combination of the above. For example it would allow m or mf, or mwf Someone said it could be done with trim but I can not figure it out.  

View 1 Replies View Related

SQL & PL/SQL :: Sequence Next Value Change?

Jul 25, 2011

We Require to Change the Sequence Next Value.

If it possible to Update the Sequence Next Value in Oracle.

View 3 Replies View Related

SQL & PL/SQL :: Change Max Partition Name

Oct 3, 2013

I want to change the partition name for my existing max value partition. I tried searching in forums but couldn't get the info.

For Example: I have partition PART_30001230, i want to change this to part_maxvalue

View 2 Replies View Related

SQL & PL/SQL :: Character Change

Jun 25, 2010

I have a table in that table ename column the data type is VARCHAR2(40).

In that column the name is like kumar's and Caño

I have created text file using spool it's showing like

Reño kumar¿s

Why only for kumar's it's showing like "¿" this.

View 36 Replies View Related

Change Systimestamp From +01:00 To +08:00

Jun 14, 2012

I need to change systimestamp from +01:00 to +08:00.

SYSTIMESTAMP
14-JUN-12 03.29.47.657901 PM +01:00 =====> I need to change it as +08:00

how can i change it?

I added TZ=SGT in my listener.ora file and bounced the listener. Even though systimestamp is still showing --> +01:00

View 3 Replies View Related

11g - Change ORACLE Home?

Aug 30, 2011

i have requirement to change the oracle home from /u04/....../dbhome_2 to /u03/...../dbhome_4

DB=11g
application version=R12.

View 1 Replies View Related

Change In Plan Even After Using Outline?

Dec 30, 2011

I observed a strange behaviour of a query after using stored outline on it. I created a stored outline for a query in one database before creating the outline i had set 2 session level parameters optimizer_index_caching and optimizer_cost adjust. i then took an export of the stored outline and then imported into another db. but here the plan of the query seems to be different from the database from which the outline was taken. why is this change? My version of oracle is 10.2.0.5.0

View 3 Replies View Related

How To Change Column Width

Dec 11, 2006

I'm having a problem with a column that is too short for it's data. It causes multiple lines for the same record. How can I change a column width in Oracle? I'm using a select statement to see the data.

View 1 Replies View Related

Database Change Management

Jan 24, 2011

I'm in the process of making a list of changes. What database application changes would be classified as standard changes and would not require a CAB meeting and which ones would be deemed critical that a CAB meeting would be required before going ahead with the change.

View 2 Replies View Related

How To Change Date Format

Nov 22, 2012

I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved