Oracle - Convert datetime formatHow do I (or can I) SELECT DISTINCT on multiple columns?How to return only the Date from a SQL Server DateTime datatypeGet list of all tables in Oracle?SQLite - UPSERT *not* INSERT or REPLACEHow do I limit the number of rows returned by an Oracle query after ordering?How can I get column names from a table in SQL Server?SQL Server query - Selecting COUNT(*) with DISTINCTWhat are the options for storing hierarchical data in a relational database?Update a column value, replacing part of a stringOracle SQL query for Date format
What does "determined" refer to in Acts 17v26?
Dates on degrees don’t make sense – will people care?
Extending prime numbers digit by digit while retaining primality
What mathematical theory is required for high frequency trading?
Can you use one creature for both convoke and delve for Hogaak?
Justifying Affordable Bespoke Spaceships
macOS: How to take a picture from camera after 1 minute
Prisoner on alien planet escapes by making up a story about ghost companions and wins the war
How did Gollum enter Moria?
Boss wants someone else to lead a project based on the idea I presented to him
What was the flower of Empress Taytu?
Is the continuity test limit resistance of a multimeter standard?
What are the pros and cons for the two possible "gear directions" when parking the car on a hill?
Second 100 amp breaker inside existing 200 amp residential panel for new detached garage
What was the first third-party commercial application for MS-DOS?
Am I legally required to provide a (GPL licensed) source code even after a project is abandoned?
Is there any proof that high saturation and contrast makes a picture more appealing in social media?
Counterfeit checks were created for my account. How does this type of fraud work?
Why do you need to heat the pan before heating the olive oil?
Why isn't my calculation that we should be able to see the sun well beyond the observable universe valid?
Can Hunter's Mark be moved after Silence has been cast on a character?
I found a password with hashcat, but it doesn't work
Is there a term for the belief that "if it's legal, it's moral"?
Mathematically modelling RC circuit with a linear input
Oracle - Convert datetime format
How do I (or can I) SELECT DISTINCT on multiple columns?How to return only the Date from a SQL Server DateTime datatypeGet list of all tables in Oracle?SQLite - UPSERT *not* INSERT or REPLACEHow do I limit the number of rows returned by an Oracle query after ordering?How can I get column names from a table in SQL Server?SQL Server query - Selecting COUNT(*) with DISTINCTWhat are the options for storing hierarchical data in a relational database?Update a column value, replacing part of a stringOracle SQL query for Date format
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a temp table.
It has last_update column in 2/10/2018 6:01:50 PM datetime format.
How can I write THE BEST QUERY to display all information that's updated on 02-Oct-2018 day?
sql oracle oracle11g
edited Apr 18 at 10:45
user7294900
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
add a comment |
I have a temp table.
It has last_update column in 2/10/2018 6:01:50 PM datetime format.
How can I write THE BEST QUERY to display all information that's updated on 02-Oct-2018 day?
sql oracle oracle11g
edited Apr 18 at 10:45
user7294900
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
1
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
1
You shouldn't be storing timestamp values in avarcharcolumn to begin with. That is a really bad idea
– a_horse_with_no_name
Mar 25 at 7:09
add a comment |
I have a temp table.
It has last_update column in 2/10/2018 6:01:50 PM datetime format.
How can I write THE BEST QUERY to display all information that's updated on 02-Oct-2018 day?
sql oracle oracle11g
edited Apr 18 at 10:45
user7294900
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
I have a temp table.
It has last_update column in 2/10/2018 6:01:50 PM datetime format.
How can I write THE BEST QUERY to display all information that's updated on 02-Oct-2018 day?
sql oracle oracle11g
sql oracle oracle11g
edited Apr 18 at 10:45
user7294900
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
edited Apr 18 at 10:45
user7294900
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
edited Apr 18 at 10:45
user7294900
26.7k123870
edited Apr 18 at 10:45
user7294900
26.7k123870
edited Apr 18 at 10:45
user7294900
26.7k123870
26.7k123870
asked Mar 25 at 7:02
leebongeeleebongee
375
asked Mar 25 at 7:02
leebongeeleebongee
375
asked Mar 25 at 7:02
leebongeeleebongee
375
375
1
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
1
You shouldn't be storing timestamp values in avarcharcolumn to begin with. That is a really bad idea
– a_horse_with_no_name
Mar 25 at 7:09
add a comment |
1
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
1
You shouldn't be storing timestamp values in avarcharcolumn to begin with. That is a really bad idea
– a_horse_with_no_name
Mar 25 at 7:09
1
1
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
1
1
You shouldn't be storing timestamp values in a
varchar column to begin with. That is a really bad idea– a_horse_with_no_name
Mar 25 at 7:09
You shouldn't be storing timestamp values in a
varchar column to begin with. That is a really bad idea– a_horse_with_no_name
Mar 25 at 7:09
add a comment |
3 Answers
3
active
oldest
votes
It is preferable to avoid TRUNC especially if you have an index on the column last_update.
A simple where condition should be better and may be better performant.
WHERE last_update >= date '2018-10-02' AND
last_update < date '2018-10-02' + 1
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
add a comment |
You can use trunc function
select *
from tab
where trunc(last_update) = date'2018-10-02'
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
add a comment |
Use trunc function for getting the same day:
trunc(last_update) = trunc(to_date('02-Oct-2018', 'DD-MONTH-YYYY'))
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
You can also use format DD-MON-YYYY
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55332659%2foracle-convert-datetime-format%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is preferable to avoid TRUNC especially if you have an index on the column last_update.
A simple where condition should be better and may be better performant.
WHERE last_update >= date '2018-10-02' AND
last_update < date '2018-10-02' + 1
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
add a comment |
It is preferable to avoid TRUNC especially if you have an index on the column last_update.
A simple where condition should be better and may be better performant.
WHERE last_update >= date '2018-10-02' AND
last_update < date '2018-10-02' + 1
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
add a comment |
It is preferable to avoid TRUNC especially if you have an index on the column last_update.
A simple where condition should be better and may be better performant.
WHERE last_update >= date '2018-10-02' AND
last_update < date '2018-10-02' + 1
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
It is preferable to avoid TRUNC especially if you have an index on the column last_update.
A simple where condition should be better and may be better performant.
WHERE last_update >= date '2018-10-02' AND
last_update < date '2018-10-02' + 1
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
answered Mar 25 at 7:30
Kaushik NayakKaushik Nayak
24.3k51433
24.3k51433
add a comment |
add a comment |
You can use trunc function
select *
from tab
where trunc(last_update) = date'2018-10-02'
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
add a comment |
You can use trunc function
select *
from tab
where trunc(last_update) = date'2018-10-02'
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
add a comment |
You can use trunc function
select *
from tab
where trunc(last_update) = date'2018-10-02'
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
You can use trunc function
select *
from tab
where trunc(last_update) = date'2018-10-02'
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
answered Mar 25 at 7:07
Barbaros ÖzhanBarbaros Özhan
17.6k71634
17.6k71634
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
add a comment |
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
Can you share a docs link for this date formatting?
– user7294900
Mar 25 at 7:09
2
2
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
@user7294900 Basic Elements of Oracle SQL
– a_horse_with_no_name
Mar 25 at 7:10
add a comment |
Use trunc function for getting the same day:
trunc(last_update) = trunc(to_date('02-Oct-2018', 'DD-MONTH-YYYY'))
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
You can also use format DD-MON-YYYY
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
add a comment |
Use trunc function for getting the same day:
trunc(last_update) = trunc(to_date('02-Oct-2018', 'DD-MONTH-YYYY'))
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
You can also use format DD-MON-YYYY
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
add a comment |
Use trunc function for getting the same day:
trunc(last_update) = trunc(to_date('02-Oct-2018', 'DD-MONTH-YYYY'))
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
You can also use format DD-MON-YYYY
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
Use trunc function for getting the same day:
trunc(last_update) = trunc(to_date('02-Oct-2018', 'DD-MONTH-YYYY'))
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
You can also use format DD-MON-YYYY
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
edited Apr 18 at 10:44
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
answered Mar 25 at 7:06
user7294900user7294900
26.7k123870
26.7k123870
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55332659%2foracle-convert-datetime-format%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
use function to_date('02-oct-2018','dd-mon-yyyy')
– Mebin Joe
Mar 25 at 7:07
1
You shouldn't be storing timestamp values in a
varcharcolumn to begin with. That is a really bad idea– a_horse_with_no_name
Mar 25 at 7:09