I am using a variable of type: DATE
Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY
I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.
My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300
Does anyone know what is wrong?
Thanks
12  3388 
I am using a variable of type: DATE
Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY
I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.
My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300
Does anyone know what is wrong?
Thanks
That shouldnt be the case.
Can you try the below query and then execute your procedure: -
-
ALTER SESSION SET nls_date_format = 'DD-MON-RR'
-
-
I'll admit to being a newbie with PL/SQL, but I did already try that. I got a syntax error. Maybe I should put it outside the PL/SQL procedure...
Well, I ran the alter session outside the procedure, then ran the procedure and same thing happens. I print out the date variable just prior to insertion, and the dates are like, 1997, 1995. Then in the table I see they have been inserted as 2097, 2095. Very strange.
I found in Toad where to make the NLS parameter setting. It still does the same thing...Geeezzz .
Did you try this: -
-
ALTER SESSION SET nls_date_format = 'DD-MON-RRRR'
-
-
With 4 R's ?
Can you post your code that you are using for INSERTING data from table A to table B?
Saii 145
Recognized Expert New Member
Just try using
to_date(to_char (<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table
I am using a variable of type: DATE
Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY
I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.
My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300
Does anyone know what is wrong?
Thanks
hi ,
try this
SQL> alter session set NLS_DATE_FORMAT = 'DD-MM-RRRR';
and try to insert the date values by converting it to
insert into table_name values ( to_date( <date column>,' RRRR-MM-DD HH:MI:SS') );
Just try using
to_date(to_char (<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table
Thanks, but that did not work. I had already tried it a while back, but it was a good suggestion.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Dominique Javet |
last post by:
Hello,
I'm new to php and mysql and I use Dreamweaver MX 2004, so sorry for this
"newbie" question... I've found no answer in the forum ...
I've a date problem with my formular. In my mysql DB my filed "date" in
table "experience" is like this: Y-m-d (2002-07-23).
My fied`date` is date, NOT NULL with no default entry
My form read well the date data depending the id, (pe. 30.02.2003), but when
I submit a new date, I receive as result...
|
by: perplexed |
last post by:
How do you convert a user inputted date to a unix timestamp before
insterting it into your database? I have a form, with a textfield for
a date that the user inputs in the format mm-dd-yyyy and three dropdow
boxes for hours, minutes, and AM/PM. All of these need to be considered
together and converted to one Unix Timestamp and then inserted to the
MYSQL date field. The type of field is INT (11) so that I can instead
of the standard...
|
by: Fons Dijkstra |
last post by:
Hello,
I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.
When I read a DATE item everything works fine, like:
|
by: Lauren Quantrell |
last post by:
I have a stored procedure using Convert where the exact same Convert
string works in the SELECT portion of the procedure but fails in the
WHERE portion.
The entire SP is listed below.
Specifically, I have a problem with this portion in the WHERE clause:
DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'...
|
by: Sparrow |
last post by:
I have created a table with the following columns...
Date(datetime),Actual (Int),Planned (Int)
I need to insert weekending dates starting from 23/04/04 looping
thru'for the next 52weeks automatically into the date column.
Then in the actual and planned colums, I need to insert a count of
some records in the table.
I will appreciate help on a SQL query to achieve this!
| | |
by: Tim Davidge |
last post by:
Hi folks, been a while since I have posted a plea for help and I think I
have forgotten everything I learnt from the helpful contributors to this
newsgroup, that said however :
I'm trying to insert a range of dates for the past say two years into the
first column of say a two column table. I've been trying to use 'current
date' and working backwards from there but for the life of me I cannot get
the syntax right. I can produce a single...
|
by: Ugur Ekinci |
last post by:
Hi ,
I have two Sql Server 2000 on seperate machines ,
First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss")
And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss")
1- Date formats are different because of SQL Collation?
2- Do regional Settings affect Sql date format?
3- (Important) When inserting a datetime into first server there is no
problem (15.12.2000 12:12:12) , but when I insert into second server (if...
|
by: Igal |
last post by:
I'm trying to insert a date value into MSSQL, the type of the sql filed
is: "smalldatetime"
and i'm trying to insert a text Variable that looks like this:
"19/02/2006".
.... SET update_date='" & Update_Date & "' ...
i get the message:
The conversion of char data type to smalldatetime data type resulted in
|
by: jesmi |
last post by:
hi
i got problem in inserting the date into the database. my requirement is that when i choose a date ie from :2007-01-01 & to :2007-12-01 then all the dates starting from 2007-02-01 upto 2007-12-01 should be inserted. while inserting year,month and day should be incremented.i tried a lot and my code only increments the month. Following is my code:
public void save(String eventDt,String toDt) throws Exception{
Connection con =...
|
by: Mark |
last post by:
Hi,
i have an application which works with date.
The regional settings of the computer (XP prof. dutch version) are set to
French (Belgium).
Asp.net and Sql server take the short date format of the regional settings
(e.g. 2/08/2007 or 13/08/2007).
I checked both: that's ok.
When i try to insert a date in a datetime field in sql server which is e.g.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
