Ask a Question

date comparison in oracle for the year ends with 00

in Education by
The date stored in my database is 01-01-1900 for field emp_doj(Data Type DATE). But while retrieving the data, the value is 01-jan-00, even though formatted with dd-mm-yyyy. I am comparing retrieved date field with some other date in SQL query. select * form persons where per_join = to_date(to_char(i.emp_doj,'DD-MM-YYYY'),'DD-MM-YYYY') Here the value of to_date(to_char(i.emp_doj,'DD-MM-YYYY'),'DD-MM-YYYY') results in 01-01-00, instead of 01-01-1900 JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
I suspect it's your default NLS settings for the IDE you are using that is merely displaying the dates with a two digit year. Oracle does not store dates in any specific format. It stores them in a binary format internally. Therefore I suggest you check the settings for your IDE and/or database and you will see the NLS date format set to DD-MM-YY, alter this and set it to DD-MM-YYYY and you will see the dates as you wish to see them. As for your query, why use TO_CHAR and then TO_DATE? If emp_doj is a date field and per_join is also a date then just compare them directly for equality. If you are trying to cut the time portion off your emp_doj values then just use TRUNC(). For examples and reference on NLS: http://www.orafaq.com/wiki/NLS More here: http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html

Related questions

0 votes
Q: SOQL date comparison for formula(date) date type
    can anybody help me with writing a SOQL query. Using "Date" data type in WHERE statement is easy, ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 19, 2022 in Education by JackTerrance
0 votes
Q: SQL syntax for Same Date Last Year
    I am using SQL Server 2014 and I have the following T-SQL query which pulls data from a View Table: SELECT * from MyView ... '2014-07-01' AND CreatedOn = '2014-07-01' AND CreatedOn...
asked Apr 13, 2022 in Education by JackTerrance
0 votes
Q: Which of the following input control represents a date (year, month, day) encoded according to ISO 8601 in Web Form 2.0?
    Which of the following input control represents a date (year, month, day) encoded according to ISO 8601 in Web Form 2.0?...
asked Apr 10, 2021 in Education by JackTerrance
0 votes
Q: Which of the following input control represents a date consisting of a year and a week number encoded according to ISO 8601 in Web Form 2.0?
    Which of the following input control represents a date consisting of a year and a week number encoded according to ISO 8601 in Web Form 2.0? A - week B - time C - number D - range...
asked Dec 2, 2020 in Technology by JackTerrance
0 votes
Q: Which of the following input control represents a date consisting of a year and a month encoded according to ISO 8601 in Web Form 2.0?
    Which of the following input control represents a date consisting of a year and a month encoded according to ISO 8601 in Web ... - datetime B - datetime-local C - date D - month...
asked Dec 1, 2020 in Technology by JackTerrance
0 votes
Q: Which of the following input control represents a date (year, month, day) encoded according to ISO 8601 in Web Form 2.0?
    Which of the following input control represents a date (year, month, day) encoded according to ISO 8601 in Web Form 2.0? A - datetime B - datetime-local C - date D - month...
asked Dec 1, 2020 in Technology by JackTerrance
0 votes
Q: How will you add 1 year to current date using local datetime api of java8 in Java8?
    How will you add 1 year to current date using local datetime api of java8 in Java8?...
asked Nov 8, 2020 in Education by Editorial Staff
0 votes
Q: How do you find current date and time in Oracle?
    How do you find current date and time in Oracle?...
asked Dec 18, 2020 in Technology by JackTerrance
0 votes
Q: How to convert a string to a date in Oracle database?
    How to convert a string to a date in Oracle database?...
asked Dec 18, 2020 in Technology by JackTerrance
0 votes
Q: How to convert a date to char in Oracle? Give one example.
    How to convert a date to char in Oracle? Give one example....
asked Dec 18, 2020 in Technology by JackTerrance
0 votes
Q: Calculate the binding energy per nucleon for `C^(12),N^(14), O^(16)`, and comment on their relative magnitudes. Masses of proton and neutron are `1.00
    Calculate the binding energy per nucleon for C 12 , N 14 , O 16 C , and comment on their relative magnitudes. Masses ... 931 M e V ) ( Select the correct answer from above options...
asked Jan 2, 2022 in Education by JackTerrance
0 votes
Q: Derive a Canonical POS expression for a Boolean function F,represented by the following truth table : 1A B C F0 0 0 01 0 0 10 1 0 11 1 0 00 0 1 01 0 1 10 1 1 0
    Derive a Canonical POS expression for a Boolean function F, represented by the following truth table : 1 A B C F 0 0 0 ... 1 0 1 1 1 1 Select the correct answer from above options...
asked Dec 23, 2021 in Education by JackTerrance
0 votes
Q: a) Derive a Canonical POS expression for a Boolean function F,represented by the following truth table : 1A B C F0 0 0 01 0 0 10 1 0 11 1 0 00 0 1 01 0 1 10 1 1 0
    a) Derive a Canonical POS expression for a Boolean function F, represented by the following truth table : 1 A B C F 0 0 ... 1 0 1 1 1 1 Select the correct answer from above options...
asked Nov 28, 2021 in Education by JackTerrance

© Copyright 2018-2023 www.Blogmepost.com. All rights reserved. Developed by Blogmepost.

...